CODEWITHSUNDEEP
sqlpostgresql
Harry
Harry

Reputation: 181

Group by dynamic time period

For my problem I'll try to create a simplified example.
Table "order":

ID |    date    | client | product | product_limit_period (in months)
1  | 2015-01-01 | Bob    | table   | 1
2  | 2015-01-31 | Bob    | table   | 1
3  | 2015-02-01 | Bob    | table   | 1
4  | 2015-01-01 | Mary   | lamb    | 12
5  | 2015-06-01 | Mary   | lamb    | 12
6  | 2016-01-01 | Mary   | lamb    | 12
7  | 2016-12-31 | Mary   | lamb    | 12

This is the result, I'd like to get:

client | product | group | count
Bob    | table   | 1     | 2       #ID 1, 2
Bob    | table   | 2     | 1       #ID 3
Mary   | lamb    | 3     | 2       #ID 4, 5
Mary   | lamb    | 4     | 2       #ID 6, 7

Every product has a limit and a limit period (in months). I need to be able to see if there are any clients that have ordered a product more than its limit allows in a certain period. The period in months might be 1 month or several years in months. It is possible that the period is 1 month, 12 months, 24 months, ... until 108 months (9 years).

I feel like I need to use some combination of window functions and group by. But I haven't figured out how.

I'm using postgres 9.1. Please let me know if there is any more information I should provide.

Any help is appreciated, even just pointing me to the right direction!

Edit:
To clarify how the grouping works: The limit period starts with the first order. Bob's first order is 2015-01-01 and so this period ends with 2015-01-31. 2015-02-01 starts a second period. A period always starts with the first day of a month and ends with the last day of a month.

Upvotes: 0

Views: 253

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51649

no need to complicate with both window and group by, just add case to either a window or group, like here:

t=# select
  client
, product
, count(1)
, string_agg(id::text,',')
from so44
group by
  client
, product
, date_trunc(case when product_limit_period = 1 then 'month' else 'year' end,date);
  client  |  product  | count | string_agg
----------+-----------+-------+------------
  Bob     |  table    |     2 | 1,2
  Bob     |  table    |     1 | 3
  Mary    |  lamb     |     2 | 4,5
  Mary    |  lamb     |     2 | 6,7
(4 rows)

sample:

t=# create table so44 (i int,"date" date,client text,product text,product_limit_period int);
CREATE TABLE
t=# copy so44 from stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1  | 2015-01-01 | Bob    | table   | 1
>> 2  | 2015-01-31 | Bob    | table   | 1
3  | 2015-02-01 | Bob    | table   | 1
4  | 2015-01-01 | Mary   | lamb    | 12
5  | 2015-06-01 | Mary   | lamb    | 12
6  | 2016-01-01 | Mary   | lamb    | 12
7  | 2016-12-31 | Mary   | lamb    | 12>> >> >> >> >>
>> \.
COPY 7

Upvotes: 1

Related Questions