SQL select multiple columns by one key and multiple conditions-CodePudding

I have a sqlite3 table which contains usage of each station if format

[id, starttime, start_station_name]
1 1601503221 name_1
2 1601503328 name_2

...

I would like to sort starttime(as timestamp) by months of the year.

SELECT start_station_name, COUNT(start_station_name) as 'jan' FROM hubway
WHERE starttime BETWEEN 1609369200 AND 1612047600
GROUP BY start_station_name

The question is how could I union multiple WHERE timestamp statement, to achieve something like this?

[start_station_name, jan, feb, mar, ...]
name_1 15 54 74...
name_2 17 23 10...
name_3 25 0 9...
...

P.S There is possibility, that there will be no usage of any station within a month, so zero value is preferred to exist.

CodePudding user response：

I first created a table of months:

CREATE TABLE months (id INT, name TEXT, start INT, end INT);

and populated it with

1 Jan 1577836800 1580515200
2 Feb 1580515200 1583020800
3 Mar 1583020800 1585699200
4 Apr 1585699200 1588291200
5 May 1588291200 1590969600
6 Jun 1590969600 1593561600
7 Jul 1593561600 1596240000
8 Aug 1596240000 1598918400
9 Sep 1598918400 1601510400
10 Oct 1601510400 1604188800
11 Nov 1604188800 1606780800
12 Dec 1606780800 1609459200

Then I was able to run

WITH by_month AS (
    SELECT
        start_station_name AS ssn,
        name AS month,
        COUNT(1) AS cnt
    FROM hubway AS h
    JOIN months AS m ON h.starttime BETWEEN m.start AND m.end
    GROUP BY ssn, month
) SELECT DISTINCT 
    by_month.ssn,
    COALESCE(Jan.cnt, 0),
    COALESCE(Feb.cnt, 0),
    COALESCE(Mar.cnt, 0),
    COALESCE(Apr.cnt, 0),
    COALESCE(May.cnt, 0),
    COALESCE(Jun.cnt, 0),
    COALESCE(Jul.cnt, 0),
    COALESCE(Aug.cnt, 0),
    COALESCE(Sep.cnt, 0),
    COALESCE(Oct.cnt, 0),
    COALESCE(Nov.cnt, 0),
    COALESCE(Dec.cnt, 0)
FROM by_month
LEFT JOIN by_month AS Jan ON Jan.ssn = by_month.ssn AND Jan.month = 'Jan'
LEFT JOIN by_month AS Feb ON Feb.ssn = by_month.ssn AND Feb.month = 'Feb'
LEFT JOIN by_month AS Mar ON Mar.ssn = by_month.ssn AND Mar.month = 'Mar'
LEFT JOIN by_month AS Apr ON Apr.ssn = by_month.ssn AND Apr.month = 'Apr'
LEFT JOIN by_month AS May ON May.ssn = by_month.ssn AND May.month = 'May'
LEFT JOIN by_month AS Jun ON Jun.ssn = by_month.ssn AND Jun.month = 'Jun'
LEFT JOIN by_month AS Jul ON Jul.ssn = by_month.ssn AND Jul.month = 'Jul'
LEFT JOIN by_month AS Aug ON Aug.ssn = by_month.ssn AND Aug.month = 'Aug'
LEFT JOIN by_month AS Sep ON Sep.ssn = by_month.ssn AND Sep.month = 'Sep'
LEFT JOIN by_month AS Oct ON Oct.ssn = by_month.ssn AND Oct.month = 'Oct'
LEFT JOIN by_month AS Nov ON Nov.ssn = by_month.ssn AND Nov.month = 'Nov'
LEFT JOIN by_month AS Dec ON Dec.ssn = by_month.ssn AND Dec.month = 'Dec';