主页 / dba / 问题 / 183015
Accepted
Ryan
Asked: 2017-08-09 19:54:27 +0800 CST

距离上次充值卡有多久了？

  • 772

我们有一张储值卡表：

CardNo   TransactionType   Amount    TransactionDate(datetime2)
         (1 for Top-up, 0 for purchasing products)
=========================================================
0001        1              1000.00    20170809(only date is displayed for brevity)
0001        0              200.00     20170810
0001        0              300.00     20170811
0001        1              200.00     20170811
0001        0              200.00     20170812

我们想知道每张卡（客户）每次购买与当时最近一次充值之间的时间。所以预期的结果是：

RowID   GapDays
================
1       Null (or 0)
2       1
3       2
4       Null
5       1

为什么 row id 为 3 有 2 天的间隔？

购买发生在充值之前。

日期列的类型为datetime2，具有“时间”部分（为简洁起见此处省略），数据按此排序。您可以使用任何索引。如有必要，将添加不存在的索引。

sql-server sql-server-2008

3 个回答

  1. Best Answer

    我在TransactionDatejust 中添加了时间部分以获得正确的交易顺序：

    create table cards(CardNo varchar(10), TransactionType int, Amount money, TransactionDate datetime2);
create index ix_cards on cards(CardNo, TransactionType, TransactionDate);
insert into cards values
('0001',        1,              1000.00,    '20170809 00:00:00'),
('0001',        0,              200.00,     '20170810 00:00:00'),
('0001',        0,              300.00,     '20170811 00:00:00'),
('0001',        1,              200.00,     '20170811 00:00:01'),
('0001',        0,              200.00,     '20170812 00:00:00');

GO

    5 行受影响

    然后，您可以使用APPLY包含所有记录的运算符来获取它 where TransactionType = 1。请注意，您必须将子查询返回的行限制为一条记录。

    set statistics xml on;

SELECT t1.CardNo, t1.TransactionType, t1.Amount, t1.TransactionDate, t3.TransactionDate,
       DATEDIFF(DAY, t3.TransactionDate, t1.TransactionDate) GapDays
FROM   cards t1
OUTER APPLY (SELECT TOP 1 t2.TransactionDate
             FROM   cards t2
             WHERE  t2.CardNo = t1.CardNo
             AND    t2.TransactionType = 1
             AND    t2.TransactionDate <= t1.TransactionDate
             ORDER BY t2.CardNo, t2.TransactionDate DESC) t3
ORDER BY t1.CardNo, t1.TransactionType, t1.TransactionDate
set statistics xml off;
GO

    卡号 | 交易类型 | 金额 | 交易日期 | 交易日期 | 空档日
:----- | --------------： | :-------- | :-------------------- | :-------------------- | ------:
0001 | 0 | 200.0000 | 2017 年 10 月 8 日 00:00:00 | 09/08/2017 00:00:00 | 1个
0001 | 0 | 300.0000 | 2017 年 11 月 8 日 00:00:00 | 09/08/2017 00:00:00 | 2个
0001 | 0 | 200.0000 | 2017 年 12 月 8 日 00:00:00 | 2017 年 11 月 8 日 00:00:01 | 1个
0001 | 1 | 1000.0000 | 09/08/2017 00:00:00 | 09/08/2017 00:00:00 | 0
0001 | 1 | 200.0000 | 2017 年 11 月 8 日 00:00:01 | 2017 年 11 月 8 日 00:00:01 | 0

    执行计划

    dbfiddle在这里

    • 3 
  2. declare @t table (CardNo varchar(100),  TransactionType int,   Amount decimal(10,2),    TransactionDate datetime2);
    insert into @t values
    ('0001',        1,              1000.00,    '20170809'),
    ('0001',        0,              200.00,     '20170810'),
    ('0001',        0,              300.00,     '20170811 00:00:00'),
    ('0001',        1,              200.00,     '20170811 00:00:01'),
    ('0001',        0,              200.00,     '20170812');



select *, datediff(day, a.top_ud_dt, TransactionDate)as gap
from @t t1 outer apply ( select max(t2.TransactionDate) as top_ud_dt
                         from @t t2 
                         where t1.CardNo = t2.CardNo
                               and t2.TransactionType = 1
                               and t2.TransactionDate <= t1.TransactionDate) a;
    • 1 
  3. What happen when you have multiple cardno ?give example

    或者，可以使用Sql server 2012 lag函数解决相同的问题。它只是为了学习窗口功能以及如何scalar expression动态

    这里不讨论性能。

    create table #cards(CardNo varchar(10), TransactionType int, Amount money
, TransactionDate datetime2);
create index ix_cards on #cards(CardNo, TransactionType, TransactionDate);
insert into #cards values
('0001', 1, 1000.00,'20170809 00:00:00'),
('0001', 0, 200.00, '20170810 00:00:00'),
('0001', 0, 300.00, '20170811 00:00:00'),
('0001', 1, 200.00, '20170811 00:00:01'),
('0001', 0, 200.00, '20170812 00:00:00');

GO
;

WITH CTE
AS (
    SELECT *
        ,CASE 
            WHEN TransactionType = 1
                THEN NULL
            ELSE lag((
                        SELECT TOP 1 TransactionDate
                        FROM #cards c1
                        WHERE TransactionType = 1
                            AND c1.TransactionDate < c.TransactionDate
                        ORDER BY c1.TransactionDate DESC
                        ), 0) OVER (
                    ORDER BY c.TransactionDate DESC
                    )
            END RecentTopUp
    FROM #cards c
    )
SELECT CardNo , TransactionType , Amount 
, TransactionDate 
    ,datediff(day, RecentTopUp, TransactionDate) GapDays
FROM cte


drop table #cards
    • 0

相关问题