Time series group by day and kind

I create a table using the command below:

CREATE TABLE IF NOT EXISTS stats (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  session_kind INTEGER NOT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)

I insert some time series data using the command below:

INSERT INTO stats (session_kind) values (?1)

Some time after having executed several times the insert command, I have some time series data as below:

id    session_kind    ts
-----------------------------------------
1     0               2020-04-18 12:59:51  // day 1
2     1               2020-04-19 12:59:52  // day 2
3     0               2020-04-19 12:59:53
4     1               2020-04-19 12:59:54
5     0               2020-04-19 12:59:55
6     2               2020-04-19 12:59:56
7     2               2020-04-19 12:59:57
8     2               2020-04-19 12:59:58
9     2               2020-04-19 12:59:59
10    0               2020-04-20 12:59:51  // day 3
11    1               2020-04-20 12:59:52
12    0               2020-04-20 12:59:53
13    1               2020-04-20 12:59:54
14    0               2020-04-20 12:59:55
15    2               2020-04-20 12:59:56
16    2               2020-04-20 12:59:57
17    2               2020-04-20 12:59:58
18    2               2020-04-21 12:59:59  // day 4

What I would like to have a command that groups my data by date from the most recent day to the least and the number of each session_kind like below (I don’t want to give any parameter to this command):

0    1    2    ts
-------------------------
0    0    1    2020-04-21  // day 4
3    2    3    2020-04-20  // day 3
2    2    4    2020-04-19  // day 2
1    0    0    2020-04-18  // day 1

How can I group my data as above?

Answer

You can do conditional aggregation:

select
    sum(session_kind= 0) session_kind_0,
    sum(session_kind= 1) session_kind_1,
    sum(session_kind= 2) session_kind_2,
    date(ts) ts_day
from mytable
group by date(ts)
order by ts_day desc

If you want something dynamic, then it might be simpler to put the results in rows rather than columns:

select date(ts) ts_day, session_kind, count(*) cnt
from mytable
group by date(ts), session_kind
order by ts_day desc, session_kind

Advertisement

Answer