每季度从财政开始算起所用的天数。

Question

我有以下问题，这是一个简单的季度数，但我希望产生一个累积计数，根据今年迄今(而不是以前的记录)。实现这一目标的唯一途径是通过指针吗？

TPO_CQuarter和TPO_CQuarter的数据类型是varchar。

预期结果：

TPO_CQuarter Days
2016-1  112
2016-2  196
2016-3  280
2016-4  364
2017-1  111
2017-2  195
2017-3  279
2017-4  363
2018-1  112
2018-2  196
2018-3  280
2018-4  298

查询：

SELECT T.TPO_CQuarter
,COUNT(DISTINCT TPO_CFecha)
FROM TH_Rotacion R
INNER JOIN TD_Tiempo T ON R.TPO_KFECHA=T.TPO_KFecha
GROUP BY T.TPO_CQuarter
ORDER BY T.TPO_CQuarter ASC

Answer 1

如果您有一个年度专栏，您可以将其表示为：

SELECT T.TPO_CQuarter,
       COUNT(DISTINCT TPO_CFecha) as days_in_quarter,
       SUM(COUNT(DISTINcT TPPO_CFecha) OVER (PARTITION BY T.TPC_CYear ORDER BY T.TPO_CQuarter) as days
FROM TH_Rotacion R INNER JOIN
     TD_Tiempo T
     ON R.TPO_KFECHA = T.TPO_KFecha
GROUP BY T.TPO_CQuarter, T.TPO_CYear
ORDER BY T.TPO_CQuarter ASC;

如果没有，可以使用LEFT()

SELECT T.TPO_CQuarter,
       COUNT(DISTINCT TPO_CFecha) as days_in_quarter,
       SUM(COUNT(DISTINCT TPPO_CFecha) OVER (PARTITION BY LEFT(T.TPO_CQuarter, 4) ORDER BY T.TPO_CQuarter) as days
FROM TH_Rotacion R INNER JOIN
     TD_Tiempo T
     ON R.TPO_KFECHA = T.TPO_KFecha
GROUP BY T.TPO_CQuarter
ORDER BY T.TPO_CQuarter ASC;

Answer 2

试试这个：

SELECT TPO_CQuarter, SUM(Days) OVER (ORDER BY TPO_CQuarter) CumSum FROM (
    SELECT T.TPO_CQuarter
    ,COUNT(DISTINCT TPO_CFecha) Days
    FROM TH_Rotacion R
    INNER JOIN TD_Tiempo T ON R.TPO_KFECHA=T.TPO_KFecha
    GROUP BY T.TPO_CQuarter
    ORDER BY T.TPO_CQuarter ASC
) a

带SUM子句的Window函数ORDER BY充当累积和。

Answer 3

您需要累积和/运行总计

SELECT T.TPO_CQuarter
   -- cumulative sum
   ,SUM(COUNT(DISTINCT TPO_CFecha))
    OVER (PARTITION BY SUBSTRING(TPO_CFecha FROM 1 FOR 4) -- reset for each year
          ORDER BY TPO_CQuarter
          ROWS UNBOUNDED PRECEDING)
FROM TH_Rotacion R
INNER JOIN TD_Tiempo T ON R.TPO_KFECHA=T.TPO_KFecha
GROUP BY T.TPO_CQuarter
ORDER BY T.TPO_CQuarter ASC