按序列號和按標志分組的SUM值-有解無憂

我們有一個帶有序列號的串列。序列將中斷，然后重新開始。

正如您在下面看到的，SalesOrderLine缺少數字 4。

SalesOrder      SalesOrderLine  MStockCode  MPrice      MBomFlag
000000000182181 1               901337      0.00000     P
000000000182181 2               901335      2476.90000  C
000000000182181 3               340151      0.00000     C
000000000182181 5               900894      0.00000     P
000000000182181 6               400379      0.00000     C
000000000182181 7               900570      600.90000   C

我要做的是MPrice按連續的數字序列總結該欄位，然后使用該MBomFlag欄位選擇“父”。

這將是上述資料的預期結果。MBomFlag如果值 =，將在該欄位上進行任何分組P

SalesOrder      SalesOrderLine  MStockCode  MPrice      MBomFlag
000000000182181 1               901337      2476.90000  P
000000000182181 5               900894      600.90000   P

什么是最好的攻擊方式？我正在嘗試使用RANK(), ROW_NUMBER(),來找出一些東西LEAD，LAG但沒有取得太大的成功

這是源資料：

CREATE TABLE #SalesOrder (
SalesOrder NVARCHAR(20),
SalesOrderLine INT,
MStockCode INT,
MPrice DECIMAL(18,2),
MBomFlag VARCHAR(1))

INSERT INTO #SalesOrder (SalesOrder, SalesOrderLine, MStockCode, MPrice, MBomFlag)
SELECT '000000000182181',   1,  '901337',   0.00000, 'P'
UNION
SELECT '000000000182181',   2,  '901335', 2476.90000, 'C'
UNION
SELECT '000000000182181',   3,  '340151',   0.00000,  'C'
UNION
SELECT '000000000182181',   5,  '900894',   0.00000,  'P'
UNION
SELECT '000000000182181',   6,  '400379',   0.00000,  'C'
UNION
SELECT '000000000182181',   7,  '900570',   2600.90000, 'C'

SELECT *
  FROM #SalesOrder

DROP TABLE #SalesOrder

uj5u.com熱心網友回復：

可以lag()用來檢測SalesOrderLine的序列什么時候有跳轉，并統計跳轉的次數。具有相同總跳數的行屬于同一組。

with u as
(select *, lag(SalesOrderLine) over (order by SalesOrderLine) as 
previousSOL
from #SalesOrder),
v as 
(select *, sum(case when SalesOrderLine = PreviousSOL  1 then 0 else 1 
end)
over (order by SalesOrderLine rows unbounded preceding) as jumps
from u
)
select min(case when MBomFlag = 'P' then SalesOrder end) as SalesOrder,
min(case when MBomFlag = 'P' then SalesOrderLine end) as SalesOrderLine,
min(case when MBomFlag = 'P' then MStockCode end) as MStockCode,
sum(MPrice) as Mprice,
'P' as MBomFlag
from v
group by jumps;

小提琴

uj5u.com熱心網友回復：

請嘗試以下解決方案。

查詢陳述句

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY(1,1) PRIMARY KEY,
SalesOrder NVARCHAR(20),
SalesOrderLine INT,
MStockCode INT,
MPrice DECIMAL(18,2),
MBomFlag VARCHAR(1))

INSERT INTO @tbl (SalesOrder, SalesOrderLine, MStockCode, MPrice, MBomFlag) VALUES
('000000000182181', 1, '901337',   0.00000, 'P'),
('000000000182181', 2, '901335', 2476.90000, 'C'),
('000000000182181', 3, '340151',   0.00000,  'C'),
('000000000182181', 5, '900894',   0.00000,  'P'),
('000000000182181', 6, '400379',   0.00000,  'C'),
('000000000182181', 7, '900570',   600.90000, 'C');
-- DDL and sample data population, end

WITH rs AS
(
    SELECT series.*,
           ns = SalesOrderLine - id
    FROM @tbl AS series
), cte AS
(
    SELECT * 
        , FIRST_VALUE(MStockCode) OVER(PARTITION BY ns ORDER BY id) AS _MStockCode
        , FIRST_VALUE(MBomFlag) OVER(PARTITION BY ns ORDER BY id) AS _MBomFlag
    FROM rs
)
SELECT MIN(cte.SalesOrder) AS SalesOrder
    , MIN(cte.SalesOrderLine) AS SalesOrderLine
    , MIN(cte._MStockCode) AS MStockCode
    , SUM(cte.MPrice) AS MPrice
    , MIN(cte._MBomFlag) AS MBomFlag
FROM cte
GROUP BY ns;

輸出

 ----------------- ---------------- ------------ --------- ---------- 
|   SalesOrder    | SalesOrderLine | MStockCode | MPrice  | MBomFlag |
 ----------------- ---------------- ------------ --------- ---------- 
| 000000000182181 |              1 |     901337 | 2476.90 | P        |
| 000000000182181 |              5 |     900894 |  600.90 | P        |
 ----------------- ---------------- ------------ --------- ----------

uj5u.com熱心網友回復：

我假設這是在每個SalesOrder基礎上發生的。這是標準的間隙和孤島。不需要遞回查詢或臨時表：

with data as (
    select *, SalesOrderLine - row_number()
        over (partition by SalesOrder order by SalesOrderLine) as grp
    from #SalesOrder
)
select
    SalesOrderLine,
    min(SalesOrderLine) as SalesOrderLine,
    min(case when MBomFlag = 'P' then MStockCode end) as MStockCode,
    min(case when MBomFlag = 'P' then MPrice end) as MPrice,
    /* doesn't assume there will be a parent -- also retains column datatype */
    min(case when MBomFlag = 'P' then MBomFlag end) as MBomFlag
from data
group by SalesOrder, grp

uj5u.com熱心網友回復：

這是一個經典的間隙和島嶼問題。

但是，在這種情況下，每個島的起點都由 a P（或不是的行C）清楚地描繪出來。所以我們不需要LAG那個。

我們只需要為每個島分配一個分組 ID，我們可以使用視窗條件來完成COUNT。然后我們簡單地按該 ID 分組。

SELECT
  pv.SalesOrder,
  SalesOrderLine = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.SalesOrderLine END),
  MStockCode = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MStockCode END),
  MPrice = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MPrice END),
  MBomFlag = MIN(CASE WHEN pv.MBomFlag <> 'C' THEN pv.MBomFlag END)
FROM (
    SELECT *,
      GroupingId = COUNT(NULLIF(t.MBomFlag, 'C')) OVER (PARTITION BY t.SalesOrder ORDER BY t.SalesOrderLine ROWS UNBOUNDED PRECEDING)
    FROM @tbl t
) pv
GROUP BY
  pv.SalesOrder,
  pv.GroupingId;

請注意，NULLIF(t.MBomFlag, 'C')如果標志為C，則回傳 null ，因此COUNT只會計算其他行。你也可以明確地使用COUNT(CASE WHEN t.MBomFlag = 'P' THEN 1 END)

資料庫<>小提琴

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

標籤：sql-server sql-server-2016 sql-server-2019

上一篇：如何按column3從不同的column1和column2組中獲取最小值和最大值？

下一篇：每次磁區值更改時的sql視窗函式