PostgreSQL › PostgreSQL - general

Counting different strings (OK%, FB%) in same table, grouped by week number

Classic

List

Threaded

7 messages Options

Alexander Farber

Counting different strings (OK%, FB%) in same table, grouped by week number

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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Remove Ads

David Johnston

Re: Counting different strings (OK%, FB%) in same table, grouped by week number

On Feb 22, 2012, at 15:36, Alexander Farber <[hidden email]> 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 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.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alexander Farber

Re: 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 <[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver-3

Re: Counting different strings (OK%, FB%) in same table, grouped by week number

In reply to this post by Alexander Farber

On 02/22/2012 12:36 PM, Alexander Farber wrote:

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
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David Johnston

Re: Counting different strings (OK%, FB%) in same table, grouped by week number

In reply to this post by Alexander Farber

-----Original Message-----
From: Alexander Farber [mailto:[hidden email]]
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 <[hidden email]> 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.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

mgould

Re: Counting different strings (OK%, FB%) in same table, grouped by week number

In reply to this post by Alexander Farber

Sent from Samsung mobile

Adrian Klaver <[hidden email]> wrote:

>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
>[hidden email]
>
>--
>Sent via pgsql-general mailing list ([hidden email])
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general
>

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Kiriakos Georgiou-2

Re: Counting different strings (OK%, FB%) in same table, grouped by week number

In reply to this post by Alexander Farber

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:

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general