sql - 使用 SQL 填充值来计算前一周的值
问题描述
样品表
CREATE TABLE SAMPLE_TABLE (WEEK DATE, TYPE VARCHAR(50), Movie VARCHAR(50), Question VARCHAR(50), Answer VARCHAR(50), value NUMBER(38,0));
样本数据
INSERT INTO sample_table VALUES('10/1/2020', 'A', 'Contaco', '1', 'N/A', '1'),
('10/1/2020', 'A', 'Contaco', '1', 'Definitely not', '4'),
('10/1/2020', 'A', 'Contaco', '1', 'Definitely', '2'),
('10/1/2020', 'A', 'Contaco', '1', 'Probably', '2'),
('10/1/2020', 'A', 'Contaco', '1', 'Maybe', '1'),
('10/8/2020', 'A', 'Contaco', '1', 'N/A', '3'),
('10/8/2020', 'A', 'Contaco', '1', 'Definitely not', '1'),
('10/8/2020', 'A', 'Contaco', '1', 'Definitely', '2'),
('10/8/2020', 'A', 'Contaco', '1', 'Probably', '4'),
('10/8/2020', 'A', 'Contaco', '1', 'Maybe', '1'),
('10/15/2020', 'A', 'Contaco', '1', 'N/A', '2'),
('10/15/2020', 'A', 'Contaco', '1', 'Definitely not', '1'),
('10/15/2020', 'A', 'Contaco', '1', 'Definitely', '2'),
('10/15/2020', 'A', 'Contaco', '1', 'Probably', '3'),
('10/15/2020', 'A', 'Contaco', '1', 'Maybe', '2'),
('10/1/2020', 'B', 'Contaco', '1', 'N/A', '1'),
('10/1/2020', 'B', 'Contaco', '1', 'Definitely not', '4'),
('10/1/2020', 'B', 'Contaco', '1', 'Definitely', '2'),
('10/1/2020', 'B', 'Contaco', '1', 'Maybe', '1'),
('10/8/2020', 'B', 'Contaco', '1', 'N/A', '3'),
('10/8/2020', 'B', 'Contaco', '1', 'Definitely', '1'),
('10/8/2020', 'B', 'Contaco', '1', 'Probably', '2'),
('10/8/2020', 'B', 'Contaco', '1', 'Maybe', '1'),
('10/15/2020', 'B', 'Contaco', '1', 'N/A', '2'),
('10/15/2020', 'B', 'Contaco', '1', 'Definitely not', '1'),
('10/15/2020', 'B', 'Contaco', '1', 'Definitely', '2'),
('10/15/2020', 'B', 'Contaco', '1', 'Maybe', '2') ;
当前查询
Select week, type, movie, question, answer, value,
LAG(value, 1, 0) OVER (PARTITION BY movie, question, answer, type ORDER BY movie, type, week ASC) AS one_week_prior_value,
LAG(value, 2, 0) OVER (PARTITION BY movie, question, answer, type ORDER BY movie, type, week ASC) AS two_week_prior_value
from sample_table ;
使用此查询 - 我正在尝试使用 lag 函数为相同类型、电影、问题和答案导出“一周前值”和“两周前值”的值。它非常适用于 Type = A 时,因为每个问题的答案都在每周的数据中。
问题是当 Type = B 时,每周所有“答案”选项都不可用。lag 函数查找上一个最非空值,或者它不为不存在的“答案”值提供当前周的值。下面的例子
两个问题:
2020 年 10 月 15 日的“一周前值”的“绝对没有”的行应该是 0 而不是 4,因为前一周没有任何“绝对没有”的值
2020 年 10 月 15 日应该有一行表示“可能”,0 表示“值”,因为它没有该周的值,但有前 2 周的值。
+--------------------------+------+---------+----------+----------------+-------+----------------------+----------------------+
| Month, Day, Year of Week | Type | Movie | Question | Answer | Value | One Week Prior Value | Two Week Prior Value |
+--------------------------+------+---------+----------+----------------+-------+----------------------+----------------------+
| 1-Oct-20 | A | Contaco | 1 | Definitely | 2 | 0 | 0 |
| 1-Oct-20 | A | Contaco | 1 | Definitely not | 4 | 0 | 0 |
| 1-Oct-20 | A | Contaco | 1 | Maybe | 1 | 0 | 0 |
| 1-Oct-20 | A | Contaco | 1 | N/A | 1 | 0 | 0 |
| 1-Oct-20 | A | Contaco | 1 | Probably | 2 | 0 | 0 |
| 8-Oct-20 | A | Contaco | 1 | Definitely | 2 | 2 | 0 |
| 8-Oct-20 | A | Contaco | 1 | Definitely not | 1 | 4 | 0 |
| 8-Oct-20 | A | Contaco | 1 | Maybe | 1 | 1 | 0 |
| 8-Oct-20 | A | Contaco | 1 | N/A | 3 | 1 | 0 |
| 8-Oct-20 | A | Contaco | 1 | Probably | 4 | 2 | 0 |
| 15-Oct-20 | A | Contaco | 1 | Definitely | 2 | 2 | 2 |
| 15-Oct-20 | A | Contaco | 1 | Definitely not | 1 | 1 | 4 |
| 15-Oct-20 | A | Contaco | 1 | Maybe | 2 | 1 | 1 |
| 15-Oct-20 | A | Contaco | 1 | N/A | 2 | 3 | 1 |
| 15-Oct-20 | A | Contaco | 1 | Probably | 3 | 4 | 2 |
| 1-Oct-20 | B | Contaco | 1 | Definitely | 2 | 0 | 0 |
| 1-Oct-20 | B | Contaco | 1 | Definitely not | 4 | 0 | 0 |
| 1-Oct-20 | B | Contaco | 1 | Maybe | 1 | 0 | 0 |
| 1-Oct-20 | B | Contaco | 1 | N/A | 1 | 0 | 0 |
| 8-Oct-20 | B | Contaco | 1 | Definitely | 1 | 2 | 0 |
| 8-Oct-20 | B | Contaco | 1 | Maybe | 1 | 1 | 0 |
| 8-Oct-20 | B | Contaco | 1 | N/A | 3 | 1 | 0 |
| 8-Oct-20 | B | Contaco | 1 | Probably | 2 | 0 | 0 |
| 15-Oct-20 | B | Contaco | 1 | Definitely | 2 | 1 | 2 |
| 15-Oct-20 | B | Contaco | 1 | Definitely not | 1 | 4 | 0 |
| 15-Oct-20 | B | Contaco | 1 | Maybe | 2 | 1 | 1 |
| 15-Oct-20 | B | Contaco | 1 | N/A | 2 | 3 | 1 |
+--------------------------+------+---------+----------+----------------+-------+----------------------+----------------------+
这就是我理想的输出应该是什么样子。关于如何使用给定的可用数据实现这一点的任何建议?我花了一段时间才发布这篇文章,所以希望它是值得的!先感谢您
- 您可以看到 2020 年 10 月 15 日 - “绝对不是”的一周前值是 0 而不是 4
- 您可以看到 2020 年 10 月 15 日 - “可能”的值为 0
+------------------+------+-----------+----------+----------------+-------+------------------+--------------------+
| Week | Type | Movie | Question | Answer | value | prior week value | 2 week prior value |
+------------------+------+-----------+----------+----------------+-------+------------------+--------------------+
| October 1, 2020 | A | Contaco| 1 | N/A | 1.000 | 0.000 | 0.000 |
| October 1, 2020 | A | Contaco| 1 | Definitely not | 4.000 | 0.000 | 0.000 |
| October 1, 2020 | A | Contaco| 1 | Definitely | 2.000 | 0.000 | 0.000 |
| October 1, 2020 | A | Contaco| 1 | Probably | 2.000 | 0.000 | 0.000 |
| October 1, 2020 | A | Contaco| 1 | Maybe | 1.000 | 0.000 | 0.000 |
| October 8, 2020 | A | Contaco| 1 | N/A | 3.000 | 1.000 | 0.000 |
| October 8, 2020 | A | Contaco| 1 | Definitely not | 1.000 | 4.000 | 0.000 |
| October 8, 2020 | A | Contaco| 1 | Definitely | 2.000 | 2.000 | 0.000 |
| October 8, 2020 | A | Contaco| 1 | Probably | 4.000 | 2.000 | 0.000 |
| October 8, 2020 | A | Contaco| 1 | Maybe | 1.000 | 1.000 | 0.000 |
| October 15, 2020 | A | Contaco| 1 | N/A | 2.000 | 3.000 | 1.000 |
| October 15, 2020 | A | Contaco| 1 | Definitely not | 1.000 | 4.000 | 4.000 |
| October 15, 2020 | A | Contaco| 1 | Definitely | 2.000 | 2.000 | 2.000 |
| October 15, 2020 | A | Contaco| 1 | Probably | 3.000 | 4.000 | 2.000 |
| October 15, 2020 | A | Contaco| 1 | Maybe | 2.000 | 1.000 | 1.000 |
| October 1, 2020 | B | Contaco| 1 | N/A | 1.000 | 0.000 | 0.000 |
| October 1, 2020 | B | Contaco| 1 | Definitely not | 4.000 | 0.000 | 0.000 |
| October 1, 2020 | B | Contaco| 1 | Definitely | 2.000 | 0.000 | 0.000 |
| October 1, 2020 | B | Contaco| 1 | Probably | 0.000 | 0.000 | 0.000 |
| October 1, 2020 | B | Contaco| 1 | Maybe | 1.000 | 0.000 | 0.000 |
| October 8, 2020 | B | Contaco| 1 | N/A | 3.000 | 1.000 | 0.000 |
| October 8, 2020 | B | Contaco| 1 | Definitely not | 0.000 | 4.000 | 0.000 |
| October 8, 2020 | B | Contaco| 1 | Definitely | 2.000 | 2.000 | 0.000 |
| October 8, 2020 | B | Contaco| 1 | Probably | 4.000 | 0.000 | 0.000 |
| October 8, 2020 | B | Contaco| 1 | Maybe | 1.000 | 1.000 | 0.000 |
| October 15, 2020 | B | Contaco| 1 | N/A | 2.000 | 3.000 | 1.000 |
| October 15, 2020 | B | Contaco| 1 | Definitely not | 1.000 | 0.000 | 4.000 |
| October 15, 2020 | B | Contaco| 1 | Definitely | 2.000 | 2.000 | 2.000 |
| October 15, 2020 | B | Contaco| 1 | Probably | 0.000 | 4.000 | 0.000 |
| October 15, 2020 | B | Contaco| 1 | Maybe | 2.000 | 1.000 | 1.000 |
+------------------+------+-----------+----------+----------------+-------+------------------+--------------------+
解决方案
您可以通过几个相关查询(相当于左自连接)来解决它:
select week, type, movie, question, answer, value
, (select any_value(value)
from sample_table
where week=a.week-7
and (movie, question, answer, type) = (a.movie, a.question, a.answer, a.type)
) prev1
, (select any_value(value)
from sample_table
where week=a.week-14
and (movie, question, answer, type) = (a.movie, a.question, a.answer, a.type)
) prev2
from sample_table a
order by movie, question, answer, type, week;
感谢您提供简单的问题重现设置!
现在,在评论中要求添加 0。我认为 SQL 太疯狂了,但是既然你在设置上投入了这么多...
with combos as (
select *
from (select distinct movie from sample_table) a
, (select distinct question from sample_table) b
, (select distinct answer from sample_table) c
, (select distinct type from sample_table) d
, (select distinct week from sample_table) e
)
select week, type, movie, question, answer
, ifnull((select any_value(value)
from sample_table
where (week, movie, question, answer, type) = (a.week, a.movie, a.question, a.answer, a.type)
), (select 0*max(value)
from sample_table
where (movie, question, answer, type) = (a.movie, a.question, a.answer, a.type)
and week<a.week
)) value
, ifnull((select any_value(value)
from sample_table
where (week, movie, question, answer, type) = (a.week-7, a.movie, a.question, a.answer, a.type)
), (select 0*max(value)
from sample_table
where (movie, question, answer, type) = (a.movie, a.question, a.answer, a.type)
and week<a.week
)) prev1
, ifnull((select any_value(value)
from sample_table
where (week, movie, question, answer, type) = (a.week-14, a.movie, a.question, a.answer, a.type)
), (select 0*max(value)
from sample_table
where (movie, question, answer, type) = (a.movie, a.question, a.answer, a.type)
and week<a.week-7
)) prev2
from combos a
order by movie, question, answer, type, week;
推荐阅读
- asp.net-mvc - asp.net MVC 5 使用 javascript AJAX 调用控制器函数
- css - 1px 边框弄乱了 100% 宽度的拆分页面
- c# - 基于日期的两个表的计数
- python - ORA-01722: 无效数字 - 带有 cx_Oracle 的 Python
- python - 如何将 jpeg 大小减小到“所需大小”?
- c# - 返回未按预期退出递归方法 - C#
- angular-cli-v6 - Angular CLI HttpParams 如何删除参数
- filter - Out-gridview 过滤器通配符
- libreoffice-calc - 如何对一系列计算求和
- linux - 将最新文件名获取到比特定时间更早的变量中