sql - 根据 SQL 条件分配季度数

Question

我正在研究季度计数器，它将按季度识别数据差距。

这是我的数据:

年季部
2017 1 A
2017 2 A
2017 3 A
2017 4 A

2018 1 A
2018 2 A

我正在尝试分配将从 current_date 中引用的季度数值(value)。
虽然 Department从表中退出，该情况下四分之一计数保持不变。

年 季度 部门 Quarter_count
2017 1 一 12
2017 2 一 11
2017 3 A 10
2017 4 A 9

2018 1 A 8
2018 2 A 7
2018 3 A 6 这个记录不存在
2018 4 A 5 这个记录不存在

2019 1 A 4
2019 2 A 3
2019 3 A 2
2019 4 A 1

我的起始代码是:

选择部门、年、季度、
ROW_NUMBER() OVER(按年份排序，按升序排列)

来自 TABLE_A
按部门、年、季度 ASC 排序

Answer 1

所以这是相当简单的数学，每年有四个季度，所以年差乘以四，减去开始年份的季度并加上当前日期的季度，再加上一个，因为我们不是从零开始的。

因此以扩展形式:

with table_a as (
SELECT column1 as year, column2 as Quarter, column3 as Department from values 
  (2017,1,'A'),(2017,2,'A'),(2017,3,'A'),(2017,4,'A')
  ,(2018,1,'A'),(2018,2,'A')
  --,(2018,3,'A'),(2018,4,'A')
  ,(2019,1,'A'),(2019,2,'A'),(2019,3,'A'),(2019,4,'A')
)
SELECT department, year, quarter
    ,YEAR(current_date) as cdy
    ,QUARTER(current_date) as cdq
    ,(cdy-year)*4 as year_diff_in_q
    ,year_diff_in_q - quarter + cdq + 1 as Quarter_count
FROM table_a
ORDER BY department, year, quarter ASC;

给出:

DEPARTMENT  YEAR    QUARTER CDY CDQ YEAR_DIFF_IN_Q  QUARTER_COUNT
A   2017    1   2019    4   8   12
A   2017    2   2019    4   8   11
A   2017    3   2019    4   8   10
A   2017    4   2019    4   8   9
A   2018    1   2019    4   4   8
A   2018    2   2019    4   4   7
A   2019    1   2019    4   0   4
A   2019    2   2019    4   0   3
A   2019    3   2019    4   0   2
A   2019    4   2019    4   0   1

因此更小:

with table_a as (
SELECT column1 as year, column2 as Quarter, column3 as Department from values 
  (2017,1,'A'),(2017,2,'A'),(2017,3,'A'),(2017,4,'A')
  ,(2018,1,'A'),(2018,2,'A')
  --,(2018,3,'A'),(2018,4,'A')
  ,(2019,1,'A'),(2019,2,'A'),(2019,3,'A'),(2019,4,'A')
)
SELECT department, year, quarter
    ,((YEAR(current_date)-year)*4) - quarter + QUARTER(current_date) + 1 as Quarter_count
FROM table_a
ORDER BY department, year, quarter ASC;

给予:

DEPARTMENT  YEAR    QUARTER QUARTER_COUNT
A   2017    1   12
A   2017    2   11
A   2017    3   10
A   2017    4   9
A   2018    1   8
A   2018    2   7
A   2019    1   4
A   2019    2   3
A   2019    3   2
A   2019    4   1