Обсуждение: Counting different strings (OK%, FB%) in same table, grouped by week number

Поиск
Список
Период
Сортировка

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

От
Alexander Farber
Дата:
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

От
David Johnston
Дата:
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

От
Alexander Farber
Дата:
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

От
Adrian Klaver
Дата:
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

От
"David Johnston"
Дата:
-----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

От
Kiriakos Georgiou
Дата:
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