postgresql - Column of counts for time intervals -


i want table constructs column tracks how many times id appears in given week. if id appears once given 1, if appears twice given 2, if appears more 2 times given 0.

id  date    2015-11-10   2015-11-25   2015-11-09 b   2015-11-10 b   2015-11-09   2015-11-05 b   2015-11-23 b   2015-11-28 b   2015-12-04   2015-11-10 b   2015-12-04   2015-12-07   2015-12-09 c   2015-11-30   2015-12-06 c   2015-10-31 c   2015-11-04 b   2015-12-01   2015-10-30   2015-12-14

the 1 week intervals given follows 

1 -  2015-10-30   2015-11-05 2 -  2015-11-06   2015-11-12 3 -  2015-11-13   2015-11-19 4 -  2015-11-20   2015-11-26 5 -  2015-11-27   2015-12-03 6 -  2015-12-04   2015-12-10 7 -  2015-12-11   2015-12-17

the table should this.

id  interval  count     1         2  b    1         0  c    1         2     2         0  b    2         2  c    2         0     3         0  b    3         0  c    3         0     4         1  b    4         1  c    4         0     5         0  b    5         2  c    5         1     6         0  b    6         2  c    6         0     7         1  b    7         0  c    7         0

the interval column doesn't have there, added clarity.

i new sql , unsure how break dates intervals. thing have grouping date , counting.

 select id ,date, count (*) frequency   data_1   group id, date  having  frequency <= 2;

looking @ data provided, trick:

select v.id,         i.interval,         coalesce((case when sub.cnt < 3 sub.cnt else 0 end), 0) count (values('a'), ('b'), ('c')) v(id) cross join generate_series(1, 7) i(interval) left join (   select id, ((date - '2015-10-30')/7 + 1)::int interval, count(*) cnt   my_table   group 1, 2) sub using (id, interval) order 2, 1;

a few words of explanation:

  • you have 3 id values here recreated values clause. if have many more or don't know beforehand id's enumerate, can replace values clause sub-query.
  • you provide specific date range on 7 weeks. since might have weeks id not present need generate series of interval values , cross join id values above. yields 21 rows looking for.
  • then calculate occurrences of ids in intervals. can subtract date date give number of days in between. subtract date of row earliest date, divide 7 interval period, add 1 make interval 1-based , convert integer. can convert counts of > 2 0 , null 0 combination of case , coalesce().
  • the query outputs interval too, otherwise have no clue data refers to. optionally, can turn column shows date range of interval.

more flexible solution

if have more ids , larger date range, can use below version first determines distinct ids , date range. note interval 0-based make calculations easier. not matters because instead of interval number, corresponding date range displayed.

with mi (   select min(date) min, ((max(date) - min(date))/7)::int intv my_table) select v.id,         to_char((mi.min + i.intv * 7)::timestamp, 'yyyy-mm-dd') || ' - ' ||         to_char((mi.min + i.intv * 7 + 6)::timestamp, 'yyyy-mm-dd') period,         coalesce((case when sub.cnt < 3 sub.cnt else 0 end), 0) count mi,      (select distinct id my_table) v cross join lateral generate_series(0, mi.intv) i(intv) left join lateral (   select id, ((date - mi.min)/7)::int intv, count(*) cnt   my_table   group 1, 2) sub using (id, intv) order 2, 1;

sqlfiddle both solutions.


Comments

Post a Comment