趣味SQL又來了這次來統計時段訂便當

sql postgresql

一級屠豬士 2023-10-12 16:08:18 ‧ 3933 瀏覽

分享至

之前有討論過,訂雞腿與排骨便當的案例.這次是兩家聯合的訂餐中心,
開放預訂的時間是 9:00~11:30, 想要做每15分鐘為一區間,來做統計,
最後來個全部統計.
測試資料如下:

create table booking (
  qty int
, ts timestamp
);

insert into booking
select ceil(random() * 4)
     , timestamp '2023-10-11 09:00:00' + interval '1 minute' * ceil(random() * 150)
  from generate_series(1,100);

insert into booking
select ceil(random() * 4)
     , timestamp '2023-10-11 10:00:00' + interval '1 minute' * ceil(random() * 60)
  from generate_series(1,60);

預期結果:

因為使用亂數產生資料,數值會不一樣是很正常的.
大家來玩看看吧.有人有意見.那改挑戰看看吧.
沒用case,也沒用union, 只用了一次 group by.

使用 date_bin() 函數的方式

select date_bin(interval '15 minutes', ts, timestamp '2023-10-11 09:00:00') as bin
     , count(*) as cnt
     , sum(qty) as sumqty
  from booking 
 group by rollup(bin)
 order by bin nulls last;

看更多先前的討論...收起先前的討論...

sd3388 iT邦好手 1 級 ‧ 2023-10-12 16:15:13 檢舉

這邊是來給大家發問的
不是來"玩玩看"

paicheng0111 iT邦大師 5 級 ‧ 2023-10-13 08:57:24 檢舉

GPT也能解？

㊣浩瀚星空㊣ iT邦大神 1 級 ‧ 2023-10-13 12:44:16 檢舉

TO:sd3388
這是這邊的傳統。我有時也會發一篇來給人家「玩玩看」
都是技術性的討論。
這裏並未限制一定得要「發問」才能發文。
有時也會有一些高手們會提出一些議題，來讓大家討論看看。

你這樣的留言會扼殺高手們及一些想學東西的人。

這只是借用了「玩玩看」的名義來討論。並非是與技術不相關的發言。
沒那個必要講話那麼衝。

一級屠豬士 iT邦大師 1 級 ‧ 2023-10-13 13:03:00 檢舉

sd3388也是出於好意,可能沒注意到以前會有這類型態的討論吧.畢竟這種討論較少.

Oo_花之舞__oO iT邦新手 1 級 ‧ 2023-10-13 14:05:09 檢舉

sd3388 講得滿有道理的其實XD

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

咖咖拉

iT邦好手 1 級 ‧ 2023-10-12 16:44:44

WITH TimeIntervals AS (
    SELECT
        date_trunc('hour', ts) + interval '15 minutes' * floor(extract(minute from ts) / 15) AS interval_start,
        qty
    FROM booking
)

SELECT
    CASE WHEN interval_start IS NOT NULL THEN to_char(interval_start, 'YYYY-MM-DD HH24:MI:SS') ELSE 'NULL' END AS ts_min,
    COUNT(qty) AS cnt,
    SUM(qty) AS sumqty
FROM TimeIntervals
GROUP BY ROLLUP (interval_start)
ORDER BY interval_start;

結果:

ts_min	cnt	sumqty
2023-10-11 09:00:00	11	22
2023-10-11 09:15:00	13	37
2023-10-11 09:30:00	6	20
2023-10-11 09:45:00	7	16
2023-10-11 10:00:00	24	62
2023-10-11 10:15:00	24	56
2023-10-11 10:30:00	23	58
2023-10-11 10:45:00	31	84
2023-10-11 11:00:00	11	30
2023-10-11 11:15:00	10	19

cnt	sumqty
160	404

回應
分享
檢舉

登入發表回應

海綿寶寶

iT邦大神 1 級 ‧ 2023-10-12 16:52:45

Google 到的答案, 難度微低...
db fiddle

貼了才看到漏掉最後一筆總計
我不會

SELECT date_trunc('hour', ts) + (floor(date_part('minute', ts) / 15) * interval '15 minute') AS ts_min, COUNT(*) cnt, sum(qty) sumqty
FROM booking
GROUP BY ts_min
order by ts_min

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

咖咖拉 iT邦好手 1 級 ‧ 2023-10-12 17:01:07 檢舉

11:30 也算嗎XDD

keeplearning iT邦新手 5 級 ‧ 2023-10-12 17:33:02 檢舉

SELECT date_trunc('hour', ts) + (floor(date_part('minute', ts) / 15) * interval '15 minute') AS ts_min,
COUNT(*) cnt, sum(qty) sumqty
FROM booking
GROUP BY cube (ts_min)
order by ts_min