Django原生查询结合PostgreSQL generate_series过滤时保留空行问题

问题解答 #

1. 你的WHERE子句确实不能放在当前位置，需要调整位置。
2. 你的实现存在两个错误：
   • 过滤条件位置错误：将右表（orders_baseentry）的过滤条件放在LEFT JOIN之后的全局WHERE子句中，相当于把LEFT JOIN退化为INNER JOIN。没有匹配的行对应的t.status为NULL，NULL = ANY(...)判断不成立，这些空行就会被过滤掉。
   • 多余的表引用：generate_series的子查询中FROM public.orders_baseentry t完全不需要，保留这行会导致时间序列按baseentry的行数重复生成，额外占用性能。

修正方案 #

两种方案均可实现过滤同时保留所有时间空行，二选一即可：

方案1：将过滤条件移到LEFT JOIN的ON子句

report = OrderBinnedStat.objects.raw("""
SELECT row_number() OVER () AS id,
       dt_range.bin,
       count(DISTINCT t.id) AS id_count,
       coalesce(avg(DISTINCT t.flow), 0) AS avg_flow,
       coalesce(sum(DISTINCT t.flow), 0) AS sum_flow
FROM (
    SELECT generate_series(%s, %s - interval '1 milliseconds', interval '%s min') AS bin
    ) dt_range
LEFT JOIN public.orders_baseentry t 
  ON t.order_dates @> dt_range.bin
  AND t.status = ANY(ARRAY['new', 'cancelled'])
GROUP  BY dt_range.bin
ORDER  BY dt_range.bin;
""", [start_dt, end_dt, minutes])

方案2：先预过滤baseentry再做关联

report = OrderBinnedStat.objects.raw("""
SELECT row_number() OVER () AS id,
       dt_range.bin,
       count(DISTINCT t.id) AS id_count,
       coalesce(avg(DISTINCT t.flow), 0) AS avg_flow,
       coalesce(sum(DISTINCT t.flow), 0) AS sum_flow
FROM (
    SELECT generate_series(%s, %s - interval '1 milliseconds', interval '%s min') AS bin
    ) dt_range
LEFT JOIN (
    SELECT * FROM public.orders_baseentry 
    WHERE status = ANY(ARRAY['new', 'cancelled'])
) t ON t.order_dates @> dt_range.bin
GROUP  BY dt_range.bin
ORDER  BY dt_range.bin;
""", [start_dt, end_dt, minutes])

补充的coalesce函数可以直接把无匹配时的NULL统计值转为0，无需后续额外判空处理。

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