Search Postgresql Archives

Re: Count of records in a row

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 10/22/13, Elliot <yields.falsehood@xxxxxxxxx> wrote:
> It looks like you already found a solution, but here's one with a CTE. I
> cobbled this together from an older query I had for doing something
> similar, for which I unfortunately lost the original source of this
> approach. Also, this implies that there is something that gives an
> ordering to these rows (in this case, the field "i").
>
> create temp table data (i int, val char);
>
> insert into data (val, i)
> values
> ('A',1),
> ('A',2),
> ('A',3),
> ('B',4),
> ('C',5),
>
> with x
> as
> (
>    select i,
>           row_number() over () as xxx,
>           val,
>           row_number() over (partition by val order by i asc)
>             - row_number() over () as d
>    from data
>    order by i
> )
> select val,
>         count(*)
> from x
> group by d,
>           val
> order by min(i)
> ;

Elliot - Thanks for this great solution; I've tested in on my data and
it gives great results.

I'd like to understand your code.  I believe I understand most of it.
Can you explain what 'd' is?

And this clause "row_number() over (partition by val order by i asc) -
row_number() over () as d"?

(Hey, while I'm at it, is there a descriptive name for "x" too?)

Thanks


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux