sql - SQL:差距和孤岛问题 - 日期不连续导致排名不准确
问题描述
这是对我提出的另一个问题的跟进。
Quarter Segment Customer *Counter*
Q1 2018 A A1 1
Q2 2018 A A1 2
Q3 2018 A A1 3
Q4 2018 B A1 1
Q1 2019 B A1 2
Q2 2019 A A1 1
Q1 2020 A A1 *1* I want 1 not 2 here because it's not consecutive (we don't have Q3 & Q4 2019)
Q2 2020 A A1 *2* I want 2 not 3 here because it reset in Q1 2020.
如果日期是连续的,则以下查询有效。我将如何调整查询以获得我正在寻找的内容?我尝试添加一个滞后 1 行的新列,并尝试检查新列中的季度值是否本质上是真实的前 1 个季度(如果是,使用计数器,如果不是,使用“1”重新启动)。但它不会重置以下记录。然后我不知道该怎么办。
select quarter, customer, segment,
row_number() over (partition by customer, segment, seqnum - seqnum_cs order by right(quarter, 4), left(quarter, 2)) as counter
from (select t.*,
row_number() over (partition by customer order by right(quarter, 4), left(quarter, 2)) as seqnum,
row_number() over (partition by customer, segment order by right(quarter, 4), left(quarter, 2)) as seqnum_cs
from t
) t
order by customer, seqnum;
解决方案
你需要修复你的数据模型!无论如何,这可以通过枚举宿舍来解决。
select quarter, customer, segment,
row_number() over (partition by customer, segment, q_seqnum - seqnum_cs order by q_seqnum) as counter
from (select t.*,
row_number() over (partition by customer, segment order by q_seqnum) as seqnum_cs
from (select t.*,
cast(right(quarter, 4) as int) * 4 + cast(substring(quarter, 2, 1) as int) as q_seqnum
from t
) t
) t
order by customer, q_seqnum;
推荐阅读
- bash - 用管道反转 if 语句
- automated-tests - 谷歌是否支持 TestCafe 和 Puppeteer?,如果是,那么为什么?
- r - 是否有正则表达式可以在两个正斜杠之间和特定字符串之后查找字符串?[R]
- python - 使用 Pygame 的 Python 游戏出现游戏画面问题
- python-3.x - 如何使用 MinGW 编译 XGBoost
- powershell - Powershell - 提供一个不包含任何空值的集合
- floating-point - 24 位二进制到单精度浮点数
- android - 我可以在不制造智能手表的情况下创建适用于智能手表的操作系统吗
- c++ - 通过 conan_cmake_run 调用时更改柯南导入命令的工作目录
- javascript - 过滤 JSON 文件并查找值