从金额大于零的行中为每个客户分配行序列

Assign the row sequence for each customer from the row where amount is greater than zero

提问人：Ramesh 提问时间：11/6/2023 最后编辑：Ramesh 更新时间：11/7/2023 访问量：57

问：

我想从金额大于零的行中为每个客户分配行序列。 排序始终应从量为零的行开始。 下面是具有预期输出 （expected_sequence） 的示例数据。

客户 trans_date 量 expected_sequence
1 2023-01-01 0元 零
1 2023-02-01 100美元 1
1 2023-03-02 200美元 2
2 2023-01-01 100美元 1
2 2023-02-01 0元 2
2 2023-03-02 150美元 3
3 2023-01-01 0元 零
3 2023-02-01 0元 零
3 2023-03-02 150美元 1
4 2023-01-01 0元 零
4 2023-02-01 0元 零
4 2023-03-02 0元 零
SQL Oracle11g Teradata

评论

0赞 Adrian Maxwell 11/6/2023
对于单个客户，金额从 0 美元到 100 美元，然后是 200 美元，然后又回到 0 美元，会发生什么情况？$0 总是 NULL 吗？
2赞 NickW 11/6/2023
您使用的是 Oracle 11g 还是 Teradata？请更正您的标签，因为这些是完全不同的产品，解决方案很可能是特定于产品的
0赞 NickW 11/6/2023
另外，为什么表中的第 5 行不应有 null 序列 - 该值为零？
0赞 Ramesh 11/6/2023
在交易序列中，第一个序列的金额应始终大于零，之后的金额可以为零或大于零。这是因为第一个序列表示初始交易，该交易必须具有正数。序列中的所有后续事务的金额都可以为零或大于零，具体取决于第 5 行应具有序列 2 的 transaction.so 的性质。
0赞 Ramesh 11/6/2023
金额始终一致，不会改变。

答：

0赞 Adrian Maxwell 11/6/2023 #1

假设当 amount 为 0 时，您始终希望 NULL

select
      customer
    , trans_date
    , amount
    , row_number() over(partition by customer
                        order by trans_date, amount) as expected_sequence
from your_table
where amount <> 0

注意：不知道 amount 是否可以小于零，所以已经使用了，如果 amount 总是 0 或正数，你可以改用。此外，如果一个日期有超过 1 个金额共享，则行号排序使用 amount 作为决胜局，这是可选的，或者可能有更好的决胜列可供使用。<> 0> 0

评论

0赞 Ramesh 11/7/2023
amount 不能始终为 null
0赞 Adrian Maxwell 11/7/2023
好的，amount 不为 null，但 amount 可以小于零吗？如果是，则使用 ，如果不是，则使用 - 它不是那么重要，适用于是或否。<> 0> 0<> 0
0赞 Ramesh 11/8/2023
金额可以小于零
0赞 d r 11/7/2023 #2

您可以使用子查询来获取每个客户的第一笔交易的日期，并从该日期开始排序。

WITH    --  S a m p l e   D a t a :
    tbl (CUSTOMER, TRANS_DATE, AMOUNT) AS
        (   Select 1, DATE '2023-01-01', 0 From Dual Union All
            Select 1, DATE '2023-02-01', 100 From Dual Union All
            Select 1, DATE '2023-03-02', 200 From Dual Union All
            
            Select 2, DATE '2023-01-01', 100 From Dual Union All
            Select 2, DATE '2023-02-01', 0 From Dual Union All
            Select 2, DATE '2023-03-02', 150 From Dual Union All
            
            Select 3, DATE '2023-01-01', 0 From Dual Union All
            Select 3, DATE '2023-02-01', 0 From Dual Union All
            Select 3, DATE '2023-03-02', 150 From Dual Union All
            
            Select 4, DATE '2023-01-01', 0 From Dual Union All
            Select 4, DATE '2023-02-01', 0 From Dual Union All
            Select 4, DATE '2023-03-02', 0 From Dual 
        )

--    M a i n   S Q L :
Select    t.CUSTOMER, t.TRANS_DATE, AMOUNT,
          Sum(Case When t.TRANS_DATE >= f.FIRST_TRANS_DATE Then 1 End) 
                Over(Partition By t.CUSTOMER Order By t.CUSTOMER, t.TRANS_DATE 
                     Rows Between Unbounded Preceding And Current Row) "SEQ"
From      tbl t
Left Join ( Select    CUSTOMER, Min(TRANS_DATE) "FIRST_TRANS_DATE"
            From      tbl
            Where     AMOUNT > 0
            Group By  CUSTOMER
          ) f ON(f.CUSTOMER = t.CUSTOMER)
Order By  t.CUSTOMER, t.TRANS_DATE
/*      R e s u  l t :
CUSTOMER    TRANS_DATE  AMOUNT      SEQ
--------  ----------  --------  -------
       1  2023-01-01         0     null
       1  2023-02-01       100        1
       1  2023-03-02       200        2
       2  2023-01-01       100        1
       2  2023-02-01         0        2
       2  2023-03-02       150        3
       3  2023-01-01         0     null
       3  2023-02-01         0     null
       3  2023-03-02       150        1
       4  2023-01-01         0     null
       4  2023-02-01         0     null
       4  2023-03-02         0     null         */

上一个：根据余额将释放分配给反向释放

下一个：尝试使用 dblink 进行连接