將資料聚合/組合在一起-有解無憂

我有一個表格，其中包含每個 ExternalID、TestCenter、Pricetype、日期和時間段的價格，如下所示，每個時間段的價格。

庫存單位	外部ID	測驗中心ID	pricetypeid	時隙	dt_date	時間價格
1003.0113.01.01.06:00	1003	113	1	06:00:00.0000000	44713	569.00
1003.0113.01.01.06:20	1003	113	1	06:20:00.0000000	44713	569.00
1003.0113.01.01.07:00	1003	113	1	07:00:00.0000000	44713	539.00
1003.0113.01.01.07:20	1003	113	1	07:20:00.0000000	44713	539.00
1003.0113.01.01.09:40	1003	113	1	09:40:00.0000000	44713	539.00
1003.0113.01.01.10:00	1003	113	1	10:00:00.0000000	44713	539.00
1003.0113.01.01.10:20	1003	113	1	10:20:00.0000000	44713	449.00
1003.0113.01.01.10:40	1003	113	1	10:40:00.0000000	44713	539.00
1003.0113.01.01.14:20	1003	113	1	14:20:00.0000000	44713	449.00
1003.0113.01.01.14:40	1003	113	1	14:40:00.0000000	44713	539.00
1003.0113.01.01.16:00	1003	113	1	16:00:00.0000000	44713	569.00
1003.0113.01.01.16:20	1003	113	1	16:20:00.0000000	44713	569.00
1003.0113.01.03.06:00	1003	113	3	06:00:00.0000000	44713	619.00
1003.0113.01.03.16:20	1003	113	3	16:20:00.0000000	44713	619.00

我正在嘗試做的是組合時間段，以便結束時間是價格變化的時間。因此，我不會寫出 06.00 到 07.00 之間的每個時間段，而是只寫一個開始時間和結束時間來表示該價格有效的時間。

行數	庫存單位	外部ID	測驗中心ID	pricetypeid	時隙	dt_date	時間價格	時間結束
1	1003.0113.01.01.06:00	1003	113	1	06:00:00.0000000	44713	569.00	07:00:00.0000000
2	1003.0113.01.01.07:00	1003	113	1	07:00:00.0000000	44713	539.00	10:20:00.0000000
3	1003.0113.01.01.10:20	1003	113	1	10:20:00.0000000	44713	449.00	10:40:00.0000000
4	1003.0113.01.01.10:40	1003	113	1	10:40:00.0000000	44713	539.00	14:20:00.0000000
5	1003.0113.01.01.14:20	1003	113	1	14:20:00.0000000	44713	449.00	14:40:00.0000000
6	1003.0113.01.01.14:40	1003	113	1	14:40:00.0000000	44713	539.00	16:00:00.0000000
7	1003.0113.01.01.16:00	1003	113	1	16:00:00.0000000	44713	569.00	23:59:00.0000000

變化的是 ExternalID、TestCenterID、Pricetypei、dt_date，當然還有時間段。

我以正確的順序對串列進行了排序，并為每個時間段添加了結束時間。但不幸的是，我不知道如何根據第二個表回圈和聚合它。

我想做的是創建一個包含起始 sku 和結束時間的表。

Create table #SKUEND
(
    SKU varchar (25),
    ENDTIME time,
    iter int,
    row_num int,
);


DECLARE @Prices int;
DECLARE @row1 INT=1;
DECLARE @ITER INT=1;
DECLARE @SKU varchar(25);
DECLARE @ENDTIME time;
DECLARE @DT as date;
select @Prices=count(*) from #test

while @row1<@Prices

BEGIN

IF @ITER=1
begin
    SET @SKU=(select top 1 sku from #test where row_num=@row1)
    SET @DT=(select top 1 dt_date from #test where row_num=@row1)
    end

IF (SELECT TOP 1 TIMEPRICE from #test where row_num=@row1)<>(SELECT TOP 1 NextPrice from #test where row_num=@row1) 
BEGIN
IF (SELECT TOP 1 dt_date from #test where row_num=@row1)<>@DT
SET @ENDTIME='23:59:59'
else
SET @ENDTIME=(select top 1 EndTime from #test where row_num=@row1)
;

INSERT into #SKUEND 
VALUES (@SKU,@ENDTIME,@ITER,@row1)
SET @ITER=0

END


set @row1 =1
set @ITER =1
END

這給了我結果：

庫存單位	時間結束
1003.0113.01.01.06:00	07:00:00.0000000
1003.0113.01.01.07:00	10:20:00.0000000
1003.0113.01.01.10:20	10:40:00.0000000
1003.0113.01.01.10:40	14:20:00.0000000
1003.0113.01.01.14:20	14:40:00.0000000
1003.0113.01.01.14:40	16:00:00.0000000
1003.0113.01.01.16:00	23:59:59.0000000
1003.0113.02.01.07:00	10:20:00.0000000
1003.0113.02.01.10:20	10:40:00.0000000
1003.0113.02.01.10:40	14:20:00.0000000
1003.0113.02.01.14:20	14:40:00.0000000
1003.0113.02.01.14:40	16:00:00.0000000

我現在遇到的問題是，當 ExternalID、TestCenteriD、Pricetypeid 或 dt_date 發生變化時，它并沒有真正重新開始。所以我認為我需要對這些列進行回圈。

uj5u.com熱心網友回復：

試試這個查詢：

WITH CTE1 AS
(
SELECT *,
CASE WHEN LAG(TimePrice,1,0.00) OVER(PARTITION BY ExternalID,TestCenterID,pricetypeid,dt_date  ORDER BY timeslot) = TimePrice THEN 0 ELSE 1 END AS flag
FROM Table1
)
SELECT ct1.SKU,ct1.ExternalID,ct1.TestCenterID,
ct1.pricetypeid,ct1.timeslot,ct1.dt_date,
ct1.TimePrice,
LEAD(timeslot,1,'23:59:00.0000000') OVER(PARTITION BY ExternalID,TestCenterID,pricetypeid,dt_date  ORDER BY timeslot) as EndTime
FROM CTE1 ct1
WHERE flag = 1;

db小提琴鏈接

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/484684.html

標籤：sql sql服务器

上一篇：使用WindowsInstaller專案發布WPF EFCore應用程式

下一篇：關于如何從另一個表中獲取隨機記錄到另一個表上的每一行的查詢的SQL查詢？