时间戳

首页 > 解决方案 > 将三分位数加入季度 SQL（从 4 个月到 3 个月）

sql - 将三分位数加入季度 SQL（从 4 个月到 3 个月）

问题描述

假设我们每年三次测量海龟的外观，以三分位数为单位（三个 4 个月的周期）。然后我们切换到季度（四个 3 个月的周期）。我想将今年的值与上一年的值进行比较。

编辑感谢史蒂夫评论。最初我在 4 个月期间使用了错误的词，称它为“三个月” 。4 个月期间的正确词是tertile或quadrimester。

+------+--------+------------+------------+----------+
| year | period |     d1     |     d2     | value_cy |
+------+--------+------------+------------+----------+
| 2018 | T1     | 2018-01-01 | 2018-04-01 |       10 |
| 2018 | T2     | 2018-05-01 | 2018-08-01 |       20 |
| 2018 | T3     | 2018-09-01 | 2018-12-01 |       30 |
| 2019 | Q1     | 2019-01-01 | 2019-03-01 |       18 |
| 2019 | Q2     | 2019-04-01 | 2019-06-01 |       16 |
| 2019 | Q3     | 2019-07-01 | 2019-09-01 |       14 |
| 2019 | Q4     | 2019-10-01 | 2019-12-01 |       12 |
+------+--------+------------+------------+----------+

这是期望的结果：

+------+--------+------------+------------+----------+----------+
| year | period |     d1     |     d2     | value_cy | value_py |
+------+--------+------------+------------+----------+----------+
| 2018 | T1     | 2018-01-01 | 2018-04-01 |       10 |          |
| 2018 | T2     | 2018-05-01 | 2018-08-01 |       20 |          |
| 2018 | T3     | 2018-09-01 | 2018-12-01 |       30 |          |
| 2019 | Q1     | 2019-01-01 | 2019-03-01 |       18 |      7.5 |
| 2019 | Q2     | 2019-04-01 | 2019-06-01 |       16 |     12.5 |
| 2019 | Q3     | 2019-07-01 | 2019-09-01 |       14 |     17.5 |
| 2019 | Q4     | 2019-10-01 | 2019-12-01 |       12 |     22.5 |
+------+--------+------------+------------+----------+----------+
where
    Q1=3/4 * T1
    Q2=1/4 * T1 + 1/2 * T2

我的努力：

我想到的想法是一个中间表，它将从三个月到几个月，然后到季度的时间段。

+--------+-------+
| period | month |
+--------+-------+
| Q1     |     1 |
| Q1     |     2 |
| Q1     |     3 |
| Q2     |     4 |
| Q2     |     5 |
| Q2     |     6 |
| Q3     |     7 |
| Q3     |     8 |
| Q3     |     9 |
| Q4     |    10 |
| Q4     |    11 |
| Q4     |    12 |
| T1     |     1 |
| T1     |     2 |
| T1     |     3 |
| T1     |     4 |
| T2     |     5 |
| T2     |     6 |
| T2     |     7 |
| T2     |     8 |
| T3     |     9 |
| T3     |    10 |
| T3     |    11 |
| T3     |    12 |
+--------+-------+

然而，我想知道它是否可以通过源表的两个自连接来完成，首先连接在最接近的可能日期相等或更早，第二个日期相等或相等或更晚。有了这两个日期及其与源日期的差异，我们可以计算结果。

标签： sqlsql-server

解决方案

假设您不想在未来继续记录每三个月的数据（即，这可以通过一次性操作来解决），并且您正在使用 SQL Server，您可以将您的三个月数据预处理为季度数据同一张表，让您的比较更容易。像下面这样的东西会给你 Q1 和 Q2 值：

-- Q1 rows
INSERT INTO data_table (year, period, d1, d2, value_cy)
SELECT year, period, d1, d2, t1_component.value_cy FROM
(
    SELECT
        year,
        'Q1' AS period,
        CONCAT(year, '-01-01') AS d1,
        CONCAT(year, '-04-01') AS d2,
    FROM
        data_table
) main
CROSS APPLY
(
    SELECT 0.75 * value_cy AS value_cy
    FROM data_table
    WHERE year = main.year
    AND period = 'T1'
) t1_component;

-- Q2 rows
INSERT INTO data_table (year, period, d1, d2, value_cy)
SELECT year, period, d1, d2, t1_component.value_cy + t2_component.value_cy FROM
(
    SELECT
        year,
        'Q2' AS period,
        CONCAT(year, '-05-01') AS d1,
        CONCAT(year, '-08-01') AS d2,
    FROM
        data_table
) main
CROSS APPLY
(
    SELECT 0.25 * value_cy AS value_cy
    FROM data_table
    WHERE year = main.year
    AND period = 'T1'
) t1_component
CROSS APPLY
(
    SELECT 0.5 * value_cy AS value_cy
    FROM data_table
    WHERE year = main.year
    AND period = 'T2'
) t2_component;

您可以根据需要通过调整上述查询来获取 Q3 和 Q4 值。一旦您获得了季度格式的所有值，您就可以进行自我 JOIN 以将上一年的结果与当年的结果一起获得，尽管由于必须在计算值上进行 JOIN，这会很慢（尽管这可能适合您的需要） ：

SELECT
    cy.year,
    cy.period,
    cy.d1,
    cy.d2,
    cy.value_cy,
    py.value_cy AS value_py
FROM data_table cy
INNER JOIN data_table py
    ON py.year-1 = cy.year
WHERE cy.period IN ('Q1', 'Q2', 'Q3', 'Q4')

或者，您可以查看PIVOT运算符，它基本上转换列标题中的行值：

 SELECT period, [2019], [2018]
 FROM
     (SELECT period, year, value_cy FROM data_table WHERE period IN ('Q1', 'Q2', 'Q3', 'Q4')) AS src
 PIVOT
     (value_cy FOR year IN ([2019], [2018])
 AS pivotTable

推荐阅读