CODEWITHSUNDEEP
sqlpostgresqlrecursioncommon-table-expressioncrosstab
Sebastian
Sebastian

Reputation: 414

Postgres recursive CTE or crosstab function

I try to generate some user statistics from a table that includes logging information.

**TABLE users**
user_id | user_name
-------------------
1       | julia
2       | bob
3       | sebastian


**TABLE logs**
user_id | action        | timepoint
------------------------------------
1       | create_quote  | 2015-01-01
1       | send_quote    | 2015-02-03
1       | create_quote  | 2015-02-02
1       | start_job     | 2015-01-15
2       | start_job     | 2015-02-23
2       | send_quote    | 2015-03-04
2       | start_job     | 2014-12-02

My desired output is the following table

user_id | username  | create_quote | send_quote | start_job
-----------------------------------------------------------
1       | julia     |2             | 1          | 1
2       | bob       |0             | 1          | 1
3       | sebastian |0             | 0          | 0

It includes all users (even if there was nothing logged), but only the actions between date '2015-01-01' and '2015-05-31'. Actions are counted/grouped by action type and user.

The SQL statement could look someting like

SELECT * FROM myfunction() WHERE to_char(timepoint, 'YY/MM') BETWEEN '15/01' AND '15/05';

Do you have any idea how to manage this? I've been trying around with CTEs and recursion as well as with the crosstab function but could not find any solution.

Upvotes: 0

Views: 661

Answers (1)

Hambone
Hambone

Reputation: 16397

I think the crosstab function would be a lot more elegant, but in the case that you don't have the extension loaded or, like me, struggle with the syntax, this is a kind of clumsy, brute-force way you could do it:

CREATE OR REPLACE FUNCTION get_stats(
    from_date date,
    thru_date date)
  RETURNS table (
    user_id integer, 
    username text, 
    create_quote bigint, 
    send_quote bigint,
    start_job bigint
  )  AS
$BODY$
  select
    l.user_id, u.username,
    sum (case when action = 'create_quote' then 1 else 0 end) as create_quote,
    sum (case when action = 'send_quote' then 1 else 0 end) as send_quote,
    sum (case when action = 'start_job' then 1 else 0 end) as start_job
  from
    logs l
    join users u on l.user_id = u.user_id
  where
    l.timepoint between from_date and thru_date
  group by
    l.user_id, u.username
$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;

And then your query would be:

select * from get_stats('2015-01-01', '2015-05-31')

Personally, I would skip the function and just create it as a query, but it's conceivable there are reasons where you would want the function wrapper.

-- EDIT --

Based on an attempted edit, I see you may be okay with a query. Also, you wanted users that have no entries.

With all of that in mind, I think this might work:

  select
    u.user_id, u.username,
    sum (case when action = 'create_quote' then 1 else 0 end) as create_quote,
    sum (case when action = 'send_quote' then 1 else 0 end) as send_quote,
    sum (case when action = 'start_job' then 1 else 0 end) as start_job
  from
    users u
    left join logs l on
       l.user_id = u.user_id and
       l.timepoint between '2015-01-01' and '2015-05-31'
  group by
    u.user_id, u.username

Upvotes: 1

Related Questions