Postgres查询分钟级股票数据 筛选ticker+日期分组行数达阈值的结果

原有SQL的错误原因 #

1. 分组维度不符合需求：你原SQL子查询按分钟级的datetime+ticker分组，而非需求要求的ticker+日期维度分组，统计的完全不是单日单股票的总条数
2. 子查询额外过滤了session_type='Live'，若你要统计单分组下全量数据的行数，该过滤条件会导致统计行数偏少
3. 子查询按错误维度分组后返回的id是Postgres非确定性返回的随机id，in匹配逻辑完全错误

正确SQL写法 #

写法1：窗口函数（推荐，性能更优，仅需扫一次表）

如果是统计单ticker+日期下所有session类型的总行数≥629，保留该分组全部记录，写法如下：

start_date = '2021-11-08'
SQL = """
SELECT datetime, datetime_utc, ticker, open, close, session_type, high, low, volume
FROM (
    SELECT *,
           COUNT(*) OVER (PARTITION BY ticker, DATE(datetime)) AS day_total_cnt
    FROM candles
    WHERE datetime >= '{} 00:00:00'::timestamp
) t
WHERE day_total_cnt >= 629
ORDER BY ticker, datetime_utc ASC
""".format(start_date)

如果仅需要统计单ticker+日期下Live类型的行数≥629，保留该分组全部类型的记录，修改窗口函数统计逻辑即可：

COUNT(*) FILTER (WHERE session_type = 'Live') OVER (PARTITION BY ticker, DATE(datetime)) AS day_live_cnt

写法2：子查询匹配分组（兼容旧版本写法）

如果你更习惯用in子查询的写法，正确逻辑如下：

start_date = '2021-11-08'
SQL = """
SELECT datetime, datetime_utc, ticker, open, close, session_type, high, low, volume
FROM candles
WHERE (ticker, DATE(datetime)) IN (
    SELECT ticker, DATE(datetime)
    FROM candles
    WHERE datetime >= '{} 00:00:00'::timestamp
    GROUP BY ticker, DATE(datetime)
    HAVING COUNT(*) >= 629 -- 如果仅统计Live行数，这里改为COUNT(*) FILTER (WHERE session_type='Live') >=629
)
AND datetime >= '{} 00:00:00'::timestamp
ORDER BY ticker, datetime_utc ASC
""".format(start_date, start_date)

效果验证 #

你提供的样例数据中，把阈值改为4时，2021-11-11的NET分组行数不足4，会被整体过滤，完全符合你给出的预期效果。

内容的提问来源于stack exchange，提问作者Wboy