不中断序列的sql分组列

qlvxas9a  于 2021-07-29  发布在  Java
关注(0)|答案(2)|浏览(412)

主要目的是按顺序对amount列后面的行进行分组,这样,如果两个相同的值之间有任何不同的值,它们将分别编号。这里是原始数据:

SELECT Area, DateA, DateB, Amount
FROM (VALUES
    ('ABC', '2019-08-18', '2019-08-18 00:07:47.000', 3.75),
    ('ABC','2019-08-19', '2019-08-19 00:08:47.000', 3.75),
    ('ABC','2019-08-20', '2019-08-20 00:09:47.000', 3.65),
    ('ABC','2019-08-21', '2019-08-21 00:09:57.000', 3.75))
    AS FeeCollection(Area, DateA, DateB, Amount)

我试过这个,但我不知道真正的问题,以一种特殊的方式数字。

DENSE_RANK() OVER(ORDER BY Area, Amount)

这是我想要达到的示例结果。我在寻找简单的逻辑来做这件事。使用游标或循环时将不会对我有效。

bkkx9g8r

bkkx9g8r1#

我相信这就是你想要的。我用 LAG 获取cte中前一行的值,然后使用窗口 COUNT 降低…的价值 ROW_NUMBER 对于具有相同连续值的每行,按1 amount :

WITH CTE AS(
    SELECT Area,
           DateA,
           DateB,
           Amount,
           LAG(Amount) OVER (PARTITION BY Area ORDER BY DateA) AS PrevAmount
    FROM (VALUES
        ('ABC', '2019-08-18', '2019-08-18 00:07:47.000', 3.75),
        ('ABC','2019-08-19', '2019-08-19 00:08:47.000', 3.75),
        ('ABC','2019-08-20', '2019-08-20 00:09:47.000', 3.65),
        ('ABC','2019-08-21', '2019-08-21 00:09:57.000', 3.75))
        AS FeeCollection(Area, DateA, DateB, Amount))
SELECT Area,
       DateA,
       DateB,
       Amount,
       ROW_NUMBER() OVER (PARTITION BY Area ORDER BY DateA) - 
       COUNT(CASE Amount WHEN PrevAmount THEN 1 END) OVER (PARTITION BY Area ORDER BY DateA
                                                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Number
FROM CTE
ORDER BY DateA;

我确实认为你 PARTITION BY 条款,您可能需要更改/删除/移动到 ORDER BY . 因为我们只有一个价值 Area 当它改变时,不可能知道它的值应该是什么。

cunj1qz1

cunj1qz12#

我会用 lag() 一个累积的总和,但看起来像:

select t.*,
       sum(case when prev_amount = amount then 0 else 1 end) over
           (partition by area order by datea) as number
from (select t.*,
             lag(amount) over (partition by area order by datea) as prev_amount
      from t
     ) t;

相关问题