sql - 连接 PostgreSQL 中每一行的所有行
问题描述
我有一个类似于以下的数据集:
CREATE TABLE revenue (
employee_id int,
job_id int,
date date,
revenue numeric (9,2)
);
INSERT INTO revenue (employee_id, job_id, date, revenue) VALUES (1, 123, '2018-07-16', 100);
INSERT INTO revenue (employee_id, job_id, date, revenue) VALUES (1, 124, '2018-07-17', 100);
INSERT INTO revenue (employee_id, job_id, date, revenue) VALUES (2, 125, '2018-07-14', 100);
INSERT INTO revenue (employee_id, job_id, date, revenue) VALUES (2, 126, '2018-07-16', 100);
我编写了以下查询并具有以下输出:
SELECT employee_id, generate_series::date, SUM(revenue) as revenue
FROM generate_series('2018-07-14'::date, '2018-07-17'::date, '1 day')
LEFT JOIN revenue ON revenue.date = generate_series::date
GROUP BY employee_id, generate_series::date
ORDER BY employee_id, generate_series::date;
+---------------+-------------------+-----------+
| employee_id | generate_series | revenue |
|---------------+-------------------+-----------|
| 1 | 2018-07-16 | 100.00 |
| 1 | 2018-07-17 | 100.00 |
| 2 | 2018-07-14 | 100.00 |
| 2 | 2018-07-16 | 100.00 |
| <null> | 2018-07-15 | <null> |
+---------------+-------------------+-----------+
SELECT 5
Time: 0.010s
super>
但我的目标是为所有员工提供所有日期,即使没有收入。
这是我想要的输出:
+---------------+-------------------+-----------+
| employee_id | generate_series | revenue |
|---------------+-------------------+-----------|
| 1 | 2018-07-14 | 0.00 |
| 1 | 2018-07-15 | 0.00 |
| 1 | 2018-07-16 | 100.00 |
| 1 | 2018-07-17 | 100.00 |
| 2 | 2018-07-14 | 100.00 |
| 2 | 2018-07-15 | 0.00 |
| 2 | 2018-07-16 | 100.00 |
| 2 | 2018-07-17 | 0.00 |
+---------------+-------------------+-----------+
有人知道如何实现吗?
解决方案
使用 across join
生成行,然后left join
引入数据:
select e.employee_id, gs.dte, coalesce(revenue, 0) as revenue
from generate_series('2018-07-14'::date, '2018-07-17'::date, '1 day') gs(dte) cross join
(select distinct employee_id from revenue) e left join
revenue r
on r.employee_id = e.employee_id and r.date = gs.dte
order by employee_id, gs.date;
我认为没有group by
必要。每个员工和日期最多只有一行。
推荐阅读
- sql-server - 如何从 SQL 或 Azure Synapse 中的多个列中获取最新的非空值
- vue.js - 如何设置 v-data-table 组顺序?
- exception - 在点击 Bigtable 时获取 OAuth 刷新令牌错误 UNAUTHORIZED ERROR
- regex - 如何将“命令行参数”样式字符串与正则表达式匹配?
- azure-java-sdk - DataLakeServiceClient 在使用 Java SDK 时抛出类型定义错误:[simple type, class com.azure.core.http.netty.NettyAsyncHttpClient]
- if-statement - 是否可以使用行号作为谷歌表格中的逻辑表达式来执行 IF/THEN 语句?
- google-bigquery - 数据流作业失败并尝试在 Bigquery 上创建 temp_dataset
- python - 使用 SCRAPY 抓取特殊字符
- react-native - 从一个导航器中的屏幕导航到另一个导航器中的屏幕
- html - 带有垂直标题的引导卡