[求助]对一个连续重复数据的复杂求和

[儒林外史]

IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb
(
csid varchar(6),  ----用户ID
sdate datetime,   ----销售时间
bs bit,           ----标识
xl int            ----销售量
)

INSERT INTO #tb
VALUES
('00001','2014-01-01 09:16:11',0,100),
('00001','2014-01-01 09:16:10',1,150),
('00001','2014-01-01 09:16:11',1,1000),
('00001','2014-01-01 09:16:12',1,260),
('00001','2014-01-01 09:16:13',1,7100),
('00001','2014-01-01 09:17:14',0,336),
('00001','2014-01-01 09:17:15',0,40),
('00001','2014-01-02 11:20:22',1,100),
('00001','2014-01-02 11:20:52',1,200),
('00001','2014-01-02 11:20:52',1,180),
('00001','2014-01-02 11:25:01',1,500),
('00001','2014-01-02 11:33:14',0,6000),
('00001','2014-01-02 11:33:52',0,20000),
('00002','2014-01-03 10:15:01',1,100),
('00002','2014-01-03 10:16:10',0,150),
('00002','2014-01-03 10:16:11',0,1000),
('00002','2014-01-03 10:16:12',0,260),
('00002','2014-01-03 10:16:13',1,7100),
('00002','2014-01-04 13:25:01',0,500),
('00002','2014-01-04 13:33:14',0,6000),
('00002','2014-01-04 13:33:52',0,20000)

我要的是如下结果：
csid rq bs counts total
00001 2014-01-01 0 1 100
00001 2014-01-01 1 4 8510
00001 2014-01-01 0 2 376
00001 2014-01-02 1 4 980
00001 2014-01-02 0 2 26000

00002 2014-01-03 1 1 100
00002 2014-01-03 0 3 1410
00002 2014-01-03 1 1 7100
00002 2014-01-04 0 3 26500

counts为某个标识连续重复的次数，total为标识没有重复的其销量不变，如果标识重复就需要对其销量求和。

[AcHerat]

--#tb csid  sdate  bs  xl
--sql2005
;with cte as
(
    select *,
        rid = row_number()
    from #tb
),cta as
(
    select *,
        ret = rid - row_number() over (partition by csid,sdate,bs order by rid)
    from cte
)

select
    csid,sdate,bs,count(1) as counts,sum(xl) as total
from cta
group by csid,sdate,bs

[儒林外史]

引用 1 楼 AcHerat 的回复:

--#tb csid  sdate  bs  xl
--sql2005
;with cte as
(
    select *,
        rid = row_number()
    from #tb
),cta as
(
    select *,
        ret = rid - row_number() over (partition by csid,sdate,bs order by rid)
    from cte
)

select
    csid,sdate,bs,count(1) as counts,sum(xl) as total
from cta
group by csid,sdate,bs

第一个rid = row_number()后面是不是漏了语句？

[AcHerat]

row_number() over() 试试！

[儒林外史]

更正一下示例中第一条记录的销售时间错误，记录已经是排序后的了，前面的记录其销售时间应该最小。

INSERT INTO #tb
VALUES
('00001','2014-01-01 09:16:09',0,100),
('00001','2014-01-01 09:16:10',1,150),
('00001','2014-01-01 09:16:11',1,1000),
('00001','2014-01-01 09:16:12',1,260),
('00001','2014-01-01 09:16:13',1,7100),
('00001','2014-01-01 09:17:14',0,336),
('00001','2014-01-01 09:17:15',0,40),
('00001','2014-01-02 11:20:22',1,100),
('00001','2014-01-02 11:20:52',1,200),
('00001','2014-01-02 11:20:52',1,180),
('00001','2014-01-02 11:25:01',1,500),
('00001','2014-01-02 11:33:14',0,6000),
('00001','2014-01-02 11:33:52',0,20000),
('00002','2014-01-03 10:15:01',1,100),
('00002','2014-01-03 10:16:10',0,150),
('00002','2014-01-03 10:16:11',0,1000),
('00002','2014-01-03 10:16:12',0,260),
('00002','2014-01-03 10:16:13',1,7100),
('00002','2014-01-04 13:25:01',0,500),
('00002','2014-01-04 13:33:14',0,6000),
('00002','2014-01-04 13:33:52',0,20000)

[儒林外史]

引用 3 楼 AcHerat 的回复:

row_number() over() 试试！

语句运行通不过

[AcHerat]

那你就直接 row_number() over (order by csid,sdate) 执行查询看看。

[LongRui888]

看着挺复杂的哦

[AcHerat]

奇怪，为啥回复的就少了几个字，over括号里的。

[儒林外史]

以上的示例数据应该是每个用户ID，它所包含的记录是已经排序排好了的。

[儒林外史]

引用 6 楼 AcHerat 的回复:

那你就直接 row_number() over (order by csid,sdate) 执行查询看看。

不能得到我要的结果。

[BzTech_123]

IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb
(
csid varchar(6),  ----用户ID
sdate datetime,   ----销售时间
bs bit,           ----标识
xl int            ----销售量
)

INSERT INTO #tb
VALUES
('00001','2014-01-01 09:16:11',0,100),
('00001','2014-01-01 09:16:10',1,150),
('00001','2014-01-01 09:16:11',1,1000),
('00001','2014-01-01 09:16:12',1,260),
('00001','2014-01-01 09:16:13',1,7100),
('00001','2014-01-01 09:17:14',0,336),
('00001','2014-01-01 09:17:15',0,40),
('00001','2014-01-02 11:20:22',1,100),
('00001','2014-01-02 11:20:52',1,200),
('00001','2014-01-02 11:20:52',1,180),
('00001','2014-01-02 11:25:01',1,500),
('00001','2014-01-02 11:33:14',0,6000),
('00001','2014-01-02 11:33:52',0,20000),
('00002','2014-01-03 10:15:01',1,100),
('00002','2014-01-03 10:16:10',0,150),
('00002','2014-01-03 10:16:11',0,1000),
('00002','2014-01-03 10:16:12',0,260),
('00002','2014-01-03 10:16:13',1,7100),
('00002','2014-01-04 13:25:01',0,500),
('00002','2014-01-04 13:33:14',0,6000),
('00002','2014-01-04 13:33:52',0,20000)

; WITH CTE_S AS(
SELECT csid, t.sdate, bs, xl, S.sdate AS GroupDate  
FROM #tb t 
OUTER APPLY (SELECT TOP 1 sdate FROM #tb WHERE sdate <t.sdate AND bs<>t.bs ORDER BY sdate DESC) S 
)

SELECT csid, CONVERT(varchar(10),sdate,120), bs, COUNT(*) AS [COUNT], SUM(xl) AS Total 
FROM CTE_S G
GROUP BY G.bs, G.GroupDate,csid, CONVERT(varchar(10),G.sdate,120) 
ORDER BY G.GroupDate, G.bs, G.csid, CONVERT(varchar(10),G.sdate,120)