Group-By
GROUP BY 日期列,然後按自定義白天分組
所以我必鬚根據日期和產品到達的時間對錶格進行分組,時間將是:
morning = [5, 6, 7 , 8, 9] mid_morning = [10, 11] midday = [12, 13, 14] evening = [15, 16, 17 ,18 ,19, 20] night = [21, 22, 23, 0, 1, 2, 3, 4]
這是表格:
CREATE TABLE inventory ( inventory_id serial PRIMARY KEY, arrive_date date NOT NULL, arrive_location character varying NOT NULL, thing_type integer NOT NULL, quantity integer NOT NULL ); INSERT INTO inventory (arrive_date, arrive_location, thing_type, quantity) VALUES ('2018-05-30 05:00:00-00', 'location_00', 3, 2) , ('2018-05-30 06:00:00-00', 'location_00', 3, 8) , ('2018-05-30 12:50:00-00', 'location_00', 5, 2) , ('2018-05-30 13:40:00-00', 'location_00', 1, 3) , ('2018-05-31 13:00:00-00', 'location_00', 4, 7) , ('2018-05-31 18:00:00-00', 'location_00', 2, 3) ;
期望的結果是得到這個表結果:
我只有按天分組的目前查詢小提琴,是否可以有日期然後是白天?
我不明白
preprocess_id
是如何產生的,以及實際小時數是如何映射到arrive_timeday
您的期望結果中的。無論如何,您都可以使用CASE
表達式或映射表來映射到數字。使用
CASE
表達式SELECT arrive_date, arrive_timeday, arrive_location , jsonb_object_agg(thing_type, total_things) FROM ( SELECT arrive_date , arrive_timeday , arrive_location , thing_type , sum(quantity) AS total_things FROM ( SELECT date_trunc('day', arrive_date) AS arrive_date , case when extract(hour from arrive_date) in (5, 6, 7 , 8, 9) then 0 when extract(hour from arrive_date) in (10, 11) then 1 when extract(hour from arrive_date) in (12, 13, 14) then 2 when extract(hour from arrive_date) in (15, 16, 17 ,18 ,19, 20) then 4 when extract(hour from arrive_date) in (21, 22, 23, 0, 1, 2, 3, 4) then 8 end arrive_timeday , arrive_location , thing_type , quantity FROM inventory ) inv GROUP BY arrive_date, arrive_timeday, arrive_location, thing_type ) sub GROUP BY arrive_date, arrive_timeday, arrive_location ORDER BY arrive_date, arrive_timeday, arrive_location;
使用映射表:
表創建:
CREATE TABLE hour_mapping ( hour_from integer NOT NULL, hour_to integer NOT NULL, timeday integer NOT NULL, descpt character varying NOT NULL ); INSERT INTO hour_mapping (hour_from, hour_to, timeday, descpt) VALUES (5, 9, 0, 'morning') , (10, 11, 1, 'mid_morning') , (12, 14, 2, 'midday') , (15, 20, 4, 'evening') , (21, 23, 8, 'night') , (0, 4, 8, 'night') ;
詢問
SELECT arrive_date, arrive_timeday, arrive_location , jsonb_object_agg(thing_type, total_things) FROM ( SELECT date_trunc('day', arrive_date) AS arrive_date , m.timeday as arrive_timeday , arrive_location , thing_type , sum(quantity) AS total_things FROM inventory inv, hour_mapping m WHERE extract(hour from inv.arrive_date) between m.hour_from and hour_to GROUP BY arrive_date, arrive_timeday, arrive_location, thing_type ) sub GROUP BY arrive_date, arrive_timeday, arrive_location ORDER BY arrive_date, arrive_timeday, arrive_location;