SQL Server - 在特定列中拆分行

Question

我想在给定分隔符的情况下拆分列的行(在我的特定情况下，它将为 0)，将列视为传统的链表(例如 Python 列表)。然后获取每个簇的众数，将簇的每个值替换为自己的众数。

假设我有下表:

| ID | Data
+----+-------
| 1  |  0
| 2  |  0
| 3  |  0
| 4  |  1
| 5  |  2
| 6  |  2
| 7  |  0
| 8  |  0
| 9  |  1
| 10 |  2
| 11 |  1
| 12 |  0

相关的 Python 列表是:

Data = [0, 0, 0, 1, 2, 2, 0, 0, 1, 2, 1, 0]

集群如下:

Cluster[0] = [1, 2, 2]
Cluster[1] = [1, 2, 1]

所需的表格输出为:

| ID | Data
+----+-------
| 1  |  0
| 2  |  0
| 3  |  0
| 4  |  2
| 5  |  2
| 6  |  2
| 7  |  0
| 8  |  0
| 9  |  1
| 10 |  1
| 11 |  1
| 12 |  0

Answer 1

您可以通过计算每个值之前零的数量然后使用窗口函数来识别模式来做到这一点:

select t.id, t.data,
       (case when data = 0 then 0
             else first_value(data) over (partition by grp order by cnt desc)
        end) as mode
from (select t.id, t.data, t.grp,
             (case when data = 0 then 0
                   else count(*) over (partition by grp, data)
              end) as cnt
      from (select t.*,
                   sum(case when data = 0 then 1 else 0 end) over (order by id) as grp
            from t
           ) t
     ) t

Here是一个 SQL fiddle 。

我不喜欢所有case when data = 0逻辑，但考虑到组的定义方式，这似乎是必要的。