Sql 使用generate_series()时，LEFT联接缺少日期间隔

发布于12月08日

我正在try 创建一份每年销售额的汇总报告，其中包含几个表，如下所示:

products
id (PK)
[... ]

Orders
id (PK)
date
product_id (FK to products

我已经编写了一个查询，其中我从从最早的订单到最近的订单构建的时间序列中进行 Select ，然后将其与两个表连接在一起，按product_id和Year时间戳分组:

SELECT 
    date_trunc('year'::text, years) AS year,
    products.id as product_id,
    coalesce(count(o.id),0) AS total_orders
   FROM generate_series(
       (select MIN(orders.date) from orders),
       (select MAX(orders.date) from orders),
       '1 year'::interval
   ) years
     left JOIN orders o ON date_trunc('year'::text, years) = date_trunc('year'::text, o.date)
     join products on products.id = o.product_id
     
  GROUP BY year, products.id
  ORDER BY year, products.id

查询的结果如下所示:

year	product_id	total_orders
2012-01-01	1	4
2013-01-01	1	3
2013-01-01	2	1

由于左连接，我希望看到另一行合并空结果为零:

year	product_id	total_orders
2012-01-01	2	0

当orders中不存在给定年份的行时，为什么时间序列上的LEFT JOIN没有作为指向零total_orders的行返回，您知道吗？

SELECT years.year AS year, products.id AS product_id, count(o.id) AS total_orders FROM generate_series( (select date_trunc('year', MIN(orders.date)) from orders), (select MAX(orders.date) from orders), '1 year') AS years(year) JOIN (select distinct id from products) AS products ON true LEFT JOIN orders o ON years.year = date_trunc('year',o.date) AND products.id = o.product_id GROUP BY year, products.id ORDER BY year, products.id

Sql 使用generate_series()时，LEFT联接缺少日期间隔

推荐答案

Sql相关问答推荐

基于前面行的值：当x&>2时重复1，当连续3行x=0时则重复0

如何在不更改S代码的情况下，判断存储过程调用了多少次clr函数？

如何根据同一表中某一列中的值重新排列行(仅输出它们)(重新排序)？

PostgreSQL基于2个COLS的任意组合 Select 唯一行

替换上一个或下一个值中的空值并添加其价格日期

如何在AWS Athena中 Select JSON数组的最后一个元素？

如何使用jsonn_populate_record()插入到包含IDENTITY列的表中

从选定记录中提取摘要作为值的划分

group by 并根据同表中其他列的某些条件获取 group by 中的某一列值

Postgresql - 如何根据阈值计算累积和

SQL 如何根据当前事件和下一个事件确定操作的持续时间？

汇总具有连续日期范围的行

Postgres数据库维护：基于GROUP BY删除旧记录

每组使用平均值来填补缺失值的SQL

将表格和字符串连接以 for each 记录生成订单项目

当该日期的至少两条记录具有相同的持续时间或至少一条记录的持续时间为 0 时，如何标记该日期的所有记录

如何将特定值从 JSON 列中的一个字段移动到 PostgreSQL 中的另一个字段？

如何在 SQL Server 中将 -13422.8450 舍入到 -13422.84

创建一个将层次 struct 级别放入列中的查询

Oracle SQL 查询自行运行，但在包装到select count(*) from ()时失败