計算沒有間隙的島嶼的大小-有解無憂

我的資料有 3 列；ID, NAME, VALUE ID 是像 rowcount 一樣的序列號我在資料中顯示了 3 個不同的 NAME，但可能有 10 有 2 個 VALUE

我想以“本地”方式對名稱和值進行分組，并回傳這些“本地”組的大小。這似乎是孤島和差距場景的一個版本，但沒有重復值的差距和孤島。我已經嘗試過各種解決島嶼和差距的方法，但到目前為止，還沒有真正的進展。

我對部分解決方案的失敗嘗試之一（Itzik Ben-Gan 的作業變體）：

WITH A
AS (SELECT ROW_NUMBER() OVER (ORDER BY id) RN,
           NAME,
           VALUE
    FROM #data
    WHERE NAME LIKE '%Joe%'),
T
AS (
SELECT ROW_NUMBER() OVER (ORDER BY RN) - RN AS Grp,
           RN
    FROM A
    WHERE VALUE = 0)
SELECT MIN(T.RN) AS [From],
       MAX(T.RN) AS [To],
       MAX(T.RN) - MIN(T.RN) AS [Length]
FROM T
GROUP BY Grp
ORDER BY MIN(T.RN);

輸出如：

姓名	價值	數數
喬	0	15
喬	1	3
喬	0	5
喬	1	19

起訴	1	23
起訴	0	17
起訴	1	4

瑪麗	0	16
瑪麗	1	10
瑪麗	0	7
瑪麗	1	19
瑪麗	0	3

資料在哪里：

ID	姓名	價值
1	喬	0
2	喬	0
3	喬	0
4	喬	0
5	喬	0
6	喬	0
7	喬	0
8	喬	0
9	喬	0
10	喬	0
11	起訴	1
12	起訴	1
13	起訴	1
14	起訴	1
15	起訴	1
16	起訴	1
17	起訴	1
18	起訴	1
19	起訴	1
20	起訴	1
21	起訴	1
22	起訴	1
23	起訴	1
24	起訴	1
25	起訴	1
26	起訴	1
27	瑪麗	0
28	瑪麗	0
29	瑪麗	0
30	瑪麗	0
31	瑪麗	0
32	瑪麗	0
33	瑪麗	0
34	瑪麗	0
35	喬	0
36	喬	0
37	喬	0
38	喬	0
39	喬	0
40	喬	1
41	喬	1
42	喬	1
43	喬	0
44	喬	0
45	喬	0
46	喬	0
47	喬	0
48	喬	1
49	喬	1
50	瑪麗	0
51	瑪麗	0
52	瑪麗	0
53	瑪麗	0
54	瑪麗	0
55	瑪麗	0
56	瑪麗	0
57	瑪麗	0
58	瑪麗	1
59	瑪麗	1
60	瑪麗	1
61	瑪麗	1
62	瑪麗	1
63	瑪麗	1
64	瑪麗	1
65	瑪麗	1
66	瑪麗	1
67	瑪麗	1
68	瑪麗	0
69	起訴	1
70	起訴	1
71	起訴	1
72	起訴	1
73	起訴	1
74	起訴	1
75	起訴	1
76	起訴	0
77	起訴	0
78	起訴	0
79	起訴	0
80	起訴	0
81	起訴	0
82	起訴	0
83	起訴	0
84	瑪麗	0
85	瑪麗	0
86	瑪麗	0
87	瑪麗	0
88	瑪麗	0
89	瑪麗	0
90	瑪麗	1
91	瑪麗	1
92	瑪麗	1
93	瑪麗	1
94	瑪麗	1
95	瑪麗	1
96	瑪麗	1
97	瑪麗	1
98	瑪麗	1
99	瑪麗	1
100	瑪麗	1
101	起訴	0
102	起訴	0
103	起訴	0
104	起訴	0
105	起訴	0
106	起訴	0
107	起訴	0
108	起訴	0
109	起訴	0
110	起訴	1
111	起訴	1
112	起訴	1
113	起訴	1
114	喬	1
115	喬	1
116	喬	1
117	喬	1
118	喬	1
119	喬	1
120	喬	1
121	喬	1
122	喬	1
123	喬	1
124	瑪麗	1
125	瑪麗	1
126	瑪麗	1
127	瑪麗	1
128	瑪麗	1
129	瑪麗	1
130	瑪麗	1
131	瑪麗	1
132	瑪麗	0
133	瑪麗	0
134	瑪麗	0
135	喬	1
136	喬	1
137	喬	1
138	喬	1
139	喬	1
140	喬	1
141	喬	1

uj5u.com熱心網友回復：

DROP TABLE IF EXISTS #data;
CREATE TABLE #data 
(
    ID INT,
    NAME VARCHAR(50),
    VALUE INT
);
INSERT INTO #data values
(1  ,'Joe', 0    ),
(2  ,'Joe', 0    ),
(3  ,'Joe', 0    ),
(4  ,'Joe', 0    ),
(5  ,'Joe', 0    ),
(6  ,'Joe', 0    ),
(7  ,'Joe', 0    ),
(8  ,'Joe', 0    ),
(9  ,'Joe', 0    ),
(10 ,'Joe', 0    ),
(11 ,'Sue', 1    ),
(12 ,'Sue', 1    ),
(13 ,'Sue', 1    ),
(14 ,'Sue', 1    ),
(15 ,'Sue', 1    ),
(16 ,'Sue', 1    ),
(17 ,'Sue', 1    ),
(18 ,'Sue', 1    ),
(19 ,'Sue', 1    ),
(20 ,'Sue', 1    ),
(21 ,'Sue', 1    ),
(22 ,'Sue', 1    ),
(23 ,'Sue', 1    ),
(24 ,'Sue', 1    ),
(25 ,'Sue', 1    ),
(26 ,'Sue', 1    ),
(27 ,'Mary',    0    ),
(28 ,'Mary',    0    ),
(29 ,'Mary',    0    ),
(30 ,'Mary',    0    ),
(31 ,'Mary',    0    ),
(32 ,'Mary',    0    ),
(33 ,'Mary',    0    ),
(34 ,'Mary',    0    ),
(35 ,'Joe', 0    ),
(36 ,'Joe', 0    ),
(37 ,'Joe', 0    ),
(38 ,'Joe', 0    ),
(39 ,'Joe', 0    ),
(40 ,'Joe', 1    ),
(41 ,'Joe', 1    ),
(42 ,'Joe', 1    ),
(43 ,'Joe', 0    ),
(44 ,'Joe', 0    ),
(45 ,'Joe', 0    ),
(46 ,'Joe', 0    ),
(47 ,'Joe', 0    ),
(48 ,'Joe', 1    ),
(49 ,'Joe', 1    ),
(50 ,'Mary',    0    ),
(51 ,'Mary',    0    ),
(52 ,'Mary',    0    ),
(53 ,'Mary',    0    ),
(54 ,'Mary',    0    ),
(55 ,'Mary',    0    ),
(56 ,'Mary',    0    ),
(57 ,'Mary',    0    ),
(58 ,'Mary',    1    ),
(59 ,'Mary',    1    ),
(60 ,'Mary',    1    ),
(61 ,'Mary',    1    ),
(62 ,'Mary',    1    ),
(63 ,'Mary',    1    ),
(64 ,'Mary',    1    ),
(65 ,'Mary',    1    ),
(66 ,'Mary',    1    ),
(67 ,'Mary',    1    ),
(68 ,'Mary',    0    ),
(69 ,'Sue', 1    ),
(70 ,'Sue', 1    ),
(71 ,'Sue', 1    ),
(72 ,'Sue', 1    ),
(73 ,'Sue', 1    ),
(74 ,'Sue', 1    ),
(75 ,'Sue', 1    ),
(76 ,'Sue', 0    ),
(77 ,'Sue', 0    ),
(78 ,'Sue', 0    ),
(79 ,'Sue', 0    ),
(80 ,'Sue', 0    ),
(81 ,'Sue', 0    ),
(82 ,'Sue', 0    ),
(83 ,'Sue', 0    ),
(84 ,'Mary',    0    ),
(85 ,'Mary',    0    ),
(86 ,'Mary',    0    ),
(87 ,'Mary',    0    ),
(88 ,'Mary',    0    ),
(89 ,'Mary',    0    ),
(90 ,'Mary',    1    ),
(91 ,'Mary',    1    ),
(92 ,'Mary',    1    ),
(93 ,'Mary',    1    ),
(94 ,'Mary',    1    ),
(95 ,'Mary',    1    ),
(96 ,'Mary',    1    ),
(97 ,'Mary',    1    ),
(98 ,'Mary',    1    ),
(99 ,'Mary',    1    ),
(100    ,'Mary',    1),
(101    ,'Sue', 0),
(102    ,'Sue', 0),
(103    ,'Sue', 0),
(104    ,'Sue', 0),
(105    ,'Sue', 0),
(106    ,'Sue', 0),
(107    ,'Sue', 0),
(108    ,'Sue', 0),
(109    ,'Sue', 0),
(110    ,'Sue', 1),
(111    ,'Sue', 1),
(112    ,'Sue', 1),
(113    ,'Sue', 1),
(114    ,'Joe', 1),
(115    ,'Joe', 1),
(116    ,'Joe', 1),
(117    ,'Joe', 1),
(118    ,'Joe', 1),
(119    ,'Joe', 1),
(120    ,'Joe', 1),
(121    ,'Joe', 1),
(122    ,'Joe', 1),
(123    ,'Joe', 1),
(124    ,'Mary',    1),
(125    ,'Mary',    1),
(126    ,'Mary',    1),
(127    ,'Mary',    1),
(128    ,'Mary',    1),
(129    ,'Mary',    1),
(130    ,'Mary',    1),
(131    ,'Mary',    1),
(132    ,'Mary',    0),
(133    ,'Mary',    0),
(134    ,'Mary',    0),
(135    ,'Joe', 1),
(136    ,'Joe', 1),
(137    ,'Joe', 1),
(138    ,'Joe', 1),
(139    ,'Joe', 1),
(140    ,'Joe', 1),
(141    ,'Joe', 1);

-- first find the changes
WITH breaks AS 
(
    SELECT ID, NAME, VALUE, 
        IIF(LAG(VALUE, 1) OVER (PARTITION BY Name ORDER BY ID) != VALUE, 1, 0) AS NewGrp 
    FROM #data
), 
-- then count how many breaks we have passed 
grouped AS 
(
    SELECT ID, NAME, VALUE, SUM(NewGrp) OVER (PARTITION BY Name ORDER BY ID) grp
    FROM breaks
)
-- group by that count
SELECT NAME, VALUE, COUNT(*)
FROM grouped
GROUP BY NAME, VALUE, grp
ORDER BY NAME, MIN(ID)

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

標籤：sql sql服务器 sql-server-2012 差距和岛屿

上一篇：對自參考表進行SQL遞回以獲得特定順序

下一篇：使用另一列分組時按月滾動季度計算