Thread: Counting different strings (OK%, FB%) in same table, grouped by week number
Counting different strings (OK%, FB%) in same table, grouped by week number
From
Alexander Farber
Date:
Hello,
I have a table holding week numbers (as strings)
and user ids starting with OK, VK, FB, GG, MR, DE
(coming through diff. soc. networks to my site):
afarber@www:~> psql
psql (8.4.9)
Type "help" for help.
pref=> select * from pref_money;
id | money | yw
-------------------------+--------+---------
OK19644992852 | 8 | 2010-44
OK21807961329 | 114 | 2010-44
FB1845091917 | 774 | 2010-44
OK172682607383 | -34 | 2010-44
VK14831014 | 14 | 2010-44
VK91770810 | 2368 | 2010-44
DE8341 | 795 | 2010-44
VK99736508 | 97 | 2010-44
I'm trying to count those different users.
For one type of users (here Facebook) it's easy:
pref=> select yw, count(*) from pref_money
where id like 'FB%' group by yw order by yw desc;
yw | count
---------+-------
2012-08 | 32
2012-07 | 32
2012-06 | 37
2012-05 | 46
2012-04 | 41
But if I want to have a table displaying all users
(a column for "FB%", a column for "OK%", etc.) -
then I either have to perform a lot of copy-paste and
vim-editing or maybe someone can give me an advice?
I've reread the having-doc at
http://www.postgresql.org/docs/8.4/static/tutorial-agg.html
and still can't figure it out...
Thank you
Alex
Re: Counting different strings (OK%, FB%) in same table, grouped by week number
From
David Johnston
Date:
On Feb 22, 2012, at 15:36, Alexander Farber <alexander.farber@gmail.com> wrote: > Hello, > > I have a table holding week numbers (as strings) > and user ids starting with OK, VK, FB, GG, MR, DE > (coming through diff. soc. networks to my site): > > afarber@www:~> psql > psql (8.4.9) > Type "help" for help. > > pref=> select * from pref_money; > > id | money | yw > -------------------------+--------+--------- > OK19644992852 | 8 | 2010-44 > OK21807961329 | 114 | 2010-44 > FB1845091917 | 774 | 2010-44 > OK172682607383 | -34 | 2010-44 > VK14831014 | 14 | 2010-44 > VK91770810 | 2368 | 2010-44 > DE8341 | 795 | 2010-44 > VK99736508 | 97 | 2010-44 > > I'm trying to count those different users. > > For one type of users (here Facebook) it's easy: > > > pref=> select yw, count(*) from pref_money > where id like 'FB%' group by yw order by yw desc; > > yw | count > ---------+------- > 2012-08 | 32 > 2012-07 | 32 > 2012-06 | 37 > 2012-05 | 46 > 2012-04 | 41 > > But if I want to have a table displaying all users > (a column for "FB%", a column for "OK%", etc.) - > then I either have to perform a lot of copy-paste and > vim-editing or maybe someone can give me an advice? > > I've reread the having-doc at > http://www.postgresql.org/docs/8.4/static/tutorial-agg.html > and still can't figure it out... > > Thank you > Alex > Straight SQL: SELECT SUM(CASE WHEN id ~ '^FB' THEN 1 ELSE 0 END) AS fb_cnt, repeat for each known type (and I generally code one for unknownas well). Depending of your use case building out the non-column version and pushing it into a PivotTable would work. There is alsoa crosstab module that you can use as well - though I have not used it myself.
Re: Counting different strings (OK%, FB%) in same table, grouped by week number
From
Alexander Farber
Date:
Thank you David -
On Wed, Feb 22, 2012 at 9:56 PM, David Johnston <polobo@yahoo.com> wrote:
> SELECT SUM(CASE WHEN id ~ '^FB' THEN 1 ELSE 0 END) AS fb_cnt, repeat for each known type (and I generally code one
forunknown as well).
>
> Depending of your use case building out the non-column version and pushing it into a PivotTable would work. There is
alsoa crosstab module that you can use as well - though I have not used it myself.
>
this works well, but I'm curious
how'd you count unknown users here?
pref=> SELECT yw,
SUM(CASE WHEN id ~ '^OK' THEN 1 ELSE 0 END) AS "Odnoklassniki",
SUM(CASE WHEN id ~ '^MR' THEN 1 ELSE 0 END) AS "Mail.ru",
SUM(CASE WHEN id ~ '^VK' THEN 1 ELSE 0 END) AS "Vkontakte",
SUM(CASE WHEN id ~ '^FB' THEN 1 ELSE 0 END) AS "Facebook",
SUM(CASE WHEN id ~ '^GG' THEN 1 ELSE 0 END) AS "Google",
SUM(CASE WHEN id ~ '^DE' THEN 1 ELSE 0 END) AS "Preferans.de",
count(*) AS "Total"
from pref_money group by yw order by yw desc;
yw | Odnoklassniki | Mail.ru | Vkontakte | Facebook | Google |
Preferans.de | Total
---------+---------------+---------+-----------+----------+--------+--------------+-------
2012-08 | 2260 | 245 | 185 | 32 | 0 |
314 | 3036
2012-07 | 3074 | 338 | 267 | 32 | 0 |
386 | 4097
2012-06 | 3044 | 328 | 288 | 37 | 0 |
393 | 4090
2012-05 | 3092 | 347 | 268 | 46 | 2 |
400 | 4155
2012-04 | 3091 | 334 | 249 | 41 | 0 |
402 | 4117
Re: Counting different strings (OK%, FB%) in same table, grouped by week number
From
Adrian Klaver
Date:
On 02/22/2012 12:36 PM, Alexander Farber wrote: > Hello, > > I have a table holding week numbers (as strings) > and user ids starting with OK, VK, FB, GG, MR, DE > (coming through diff. soc. networks to my site): > > afarber@www:~> psql > psql (8.4.9) > Type "help" for help. > > pref=> select * from pref_money; > > id | money | yw > -------------------------+--------+--------- > OK19644992852 | 8 | 2010-44 > OK21807961329 | 114 | 2010-44 > FB1845091917 | 774 | 2010-44 > OK172682607383 | -34 | 2010-44 > VK14831014 | 14 | 2010-44 > VK91770810 | 2368 | 2010-44 > DE8341 | 795 | 2010-44 > VK99736508 | 97 | 2010-44 > > I'm trying to count those different users. > > For one type of users (here Facebook) it's easy: > > > pref=> select yw, count(*) from pref_money > where id like 'FB%' group by yw order by yw desc; > > yw | count > ---------+------- > 2012-08 | 32 > 2012-07 | 32 > 2012-06 | 37 > 2012-05 | 46 > 2012-04 | 41 > > But if I want to have a table displaying all users > (a column for "FB%", a column for "OK%", etc.) - > then I either have to perform a lot of copy-paste and > vim-editing or maybe someone can give me an advice? > > I've reread the having-doc at > http://www.postgresql.org/docs/8.4/static/tutorial-agg.html > and still can't figure it out... How about?: test=> \d storage_test Table "public.storage_test" Column | Type | Modifiers ---------+-----------------------+----------- fld_1 | character varying | fld_2 | character varying(10) | fld_3 | character(5) | fld_int | integer test=> SELECT * from storage_test ; fld_1 | fld_2 | fld_3 | fld_int -------+-------+-------+--------- FB001 | one | | 4 FB002 | three | | 10 OK001 | three | | 5 OK002 | two | | 6 VK001 | one | | 9 VK002 | four | | 2 test=> SELECT substring(fld_1 from 1 for 2) as id_tag,fld_2, count(*) from storage_test group by substring(fld_1 from 1 for 2),fld_2; id_tag | fld_2 | count --------+-------+------- VK | four | 1 VK | one | 1 FB | one | 1 FB | three | 1 OK | two | 1 OK | three | 1 > > Thank you > Alex > -- Adrian Klaver adrian.klaver@gmail.com
Re: Counting different strings (OK%, FB%) in same table, grouped by week number
From
"David Johnston"
Date:
-----Original Message-----
From: Alexander Farber [mailto:alexander.farber@gmail.com]
Sent: Wednesday, February 22, 2012 4:10 PM
To: David Johnston
Cc: pgsql-general
Subject: Re: [GENERAL] Counting different strings (OK%, FB%) in same table,
grouped by week number
Thank you David -
On Wed, Feb 22, 2012 at 9:56 PM, David Johnston <polobo@yahoo.com> wrote:
> SELECT SUM(CASE WHEN id ~ '^FB' THEN 1 ELSE 0 END) AS fb_cnt, repeat for
each known type (and I generally code one for unknown as well).
>
> Depending of your use case building out the non-column version and pushing
it into a PivotTable would work. There is also a crosstab module that you
can use as well - though I have not used it myself.
>
this works well, but I'm curious
how'd you count unknown users here?
pref=> SELECT yw,
SUM(CASE WHEN id ~ '^OK' THEN 1 ELSE 0 END) AS "Odnoklassniki", SUM(CASE
WHEN id ~ '^MR' THEN 1 ELSE 0 END) AS "Mail.ru", SUM(CASE WHEN id ~ '^VK'
THEN 1 ELSE 0 END) AS "Vkontakte", SUM(CASE WHEN id ~ '^FB' THEN 1 ELSE 0
END) AS "Facebook", SUM(CASE WHEN id ~ '^GG' THEN 1 ELSE 0 END) AS "Google",
SUM(CASE WHEN id ~ '^DE' THEN 1 ELSE 0 END) AS "Preferans.de",
count(*) AS "Total"
from pref_money group by yw order by yw desc;
yw | Odnoklassniki | Mail.ru | Vkontakte | Facebook | Google |
Preferans.de | Total
---------+---------------+---------+-----------+----------+--------+--------
------+-------
2012-08 | 2260 | 245 | 185 | 32 | 0 |
314 | 3036
2012-07 | 3074 | 338 | 267 | 32 | 0 |
386 | 4097
2012-06 | 3044 | 328 | 288 | 37 | 0 |
393 | 4090
2012-05 | 3092 | 347 | 268 | 46 | 2 |
400 | 4155
2012-04 | 3091 | 334 | 249 | 41 | 0 |
402 | 4117
----------------------------------------------------------------------------
-------
Brute Force:
When id does not match the expression "starts with one of the following:
'OK', 'MR', etc..."
CASE WHEN id !~ '^(OK|MR|VK|FB|GG|DE)' THEN 1 ELSE 0 END AS "Undefined"
David J.
Re: Counting different strings (OK%, FB%) in same table, grouped by week number
From
Kiriakos Georgiou
Date:
I'd code it more general to allow for any user type: select yw, substr(id,1,2) as user_type, count(1) from pref_money group by yw, user_type You can use some clever pivoting to get the user_types to be columns, but I see no need to waste db cycles. You can get the report you want by one-pass processing of the above result set. If you have mountains of data I'd precompute, before insert or during insert by a trigger, the user_type and store it separately. Kiriakos http://www.mockbites.com On Feb 22, 2012, at 3:36 PM, Alexander Farber wrote: > Hello, > > I have a table holding week numbers (as strings) > and user ids starting with OK, VK, FB, GG, MR, DE > (coming through diff. soc. networks to my site): > > afarber@www:~> psql > psql (8.4.9) > Type "help" for help. > > pref=> select * from pref_money; > > id | money | yw > -------------------------+--------+--------- > OK19644992852 | 8 | 2010-44 > OK21807961329 | 114 | 2010-44 > FB1845091917 | 774 | 2010-44 > OK172682607383 | -34 | 2010-44 > VK14831014 | 14 | 2010-44 > VK91770810 | 2368 | 2010-44 > DE8341 | 795 | 2010-44 > VK99736508 | 97 | 2010-44 > > I'm trying to count those different users. > > For one type of users (here Facebook) it's easy: > > > pref=> select yw, count(*) from pref_money > where id like 'FB%' group by yw order by yw desc; > > yw | count > ---------+------- > 2012-08 | 32 > 2012-07 | 32 > 2012-06 | 37 > 2012-05 | 46 > 2012-04 | 41 > > But if I want to have a table displaying all users > (a column for "FB%", a column for "OK%", etc.) - > then I either have to perform a lot of copy-paste and > vim-editing or maybe someone can give me an advice? > > I've reread the having-doc at > http://www.postgresql.org/docs/8.4/static/tutorial-agg.html > and still can't figure it out... > > Thank you > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general