Thread: Analytic type functionality, matching patters in a column then increment an integer

Analytic type functionality, matching patters in a column then increment an integer

From

Henry Drexler

Date:

05 October 2011, 15:30:10

I can do this in excel with vba, though due to the volume of data that is now impracticable and I am trying to move most of my logic into the query and db for analysis.

Looking at the analytic functions I see no way to carry values over the way they need to be.

Example column:

I have a column that evaluates to either tf,tt,ft,ff (true false matches).

tf means the start of a section,

tt mans intermediary but in the good section

ft means the end of the section that needs to be marked.

ff is just ignored

so I can have

'evaluation'

and would like to have a column indicate like this:

'evaluation' 'indicator'

tf 1

tt 1

ft 1

tf 2

ft 2

tf 3

tt 3

ft 3

I have tried rank() and some case statements though I can quite seem to get the sql to be aware across rows as shown in the desired indicator column noted above.

It seems like I am missing something that would we aware like that. Any ideas? Thanks in advance.

Re: Analytic type functionality, matching patters in a column then increment an integer

From

Richard Huxton

Date:

05 October 2011, 16:22:33

On 05/10/11 19:29, Henry Drexler wrote:
>
> and would like to have a column indicate like this:
>
> 'evaluation' 'indicator'
> tf                     1
> tt                     1
> ft                      1
> ff
> ff
> tf                      2
> ft                      2
> tf                      3
> tt                      3
> ft                      3
> ff

SELECT id,evaluation,sum(case when evaluation='tf' then 1 else 0 end)
over (order by id) FROM tfcount ORDER BY id;

id | evaluation | sum
----+------------+-----
   1 | tf         |   1
   2 | tt         |   1
   3 | ft         |   1
   4 | ff         |   1
   5 | ff         |   1
   6 | tf         |   2
   7 | ft         |   2
   8 | tf         |   3
   9 | tt         |   3
  10 | ft         |   3
  11 | ff         |   3
(11 rows)

OK, so that's almost it, but you'd like "ff" to be null. You probably
can do it with a suitably nested CASE, but it's probably clearer as a
sub-query.

SELECT
   id,
   evaluation,
   CASE WHEN evaluation='ff' THEN null::int
   ELSE sum::int END AS section_num
FROM (
   SELECT
     id,
     evaluation,
     sum(case when evaluation='tf' then 1 else 0 end) over (order by id)
   FROM tfcount
) AS rows
ORDER BY id;

HTH

P.S. - I always find the windowing function syntax confusing, but it's
as the standards define I believe.

--
   Richard Huxton
   Archonet Ltd

Re: Analytic type functionality, matching patters in a column then increment an integer

From

Henry Drexler

Date:

05 October 2011, 17:03:02

that was spot on Richard. Thank you for your time and the solution.

On Wed, Oct 5, 2011 at 3:22 PM, Richard Huxton <dev@archonet.com> wrote:

On 05/10/11 19:29, Henry Drexler wrote:

and would like to have a column indicate like this:

'evaluation' 'indicator'
tf 1
tt 1
ft 1
ff
ff
tf 2
ft 2
tf 3
tt 3
ft 3
ff

SELECT id,evaluation,sum(case when evaluation='tf' then 1 else 0 end) over (order by id) FROM tfcount ORDER BY id;

id | evaluation | sum
----+------------+-----
1 | tf | 1
2 | tt | 1
3 | ft | 1
4 | ff | 1
5 | ff | 1
6 | tf | 2
7 | ft | 2
8 | tf | 3
9 | tt | 3
10 | ft | 3
11 | ff | 3
(11 rows)

OK, so that's almost it, but you'd like "ff" to be null. You probably can do it with a suitably nested CASE, but it's probably clearer as a sub-query.

SELECT
id,
evaluation,
CASE WHEN evaluation='ff' THEN null::int
ELSE sum::int END AS section_num
FROM (
SELECT
id,
evaluation,
sum(case when evaluation='tf' then 1 else 0 end) over (order by id)
FROM tfcount
) AS rows
ORDER BY id;

HTH

P.S. - I always find the windowing function syntax confusing, but it's as the standards define I believe.

--
Richard Huxton
Archonet Ltd