使用子組限制創建行組

我最初在 SO 中發布了這個，但我認為在這裡更合適。

我在 sqlite 數據庫中有一個視圖，例如：

SQL 小提琴（http://www.sqlfiddle.com/#!5/ae95b/1）

這表示進入每個框的框和項目的列表。這些項目始終按box_start範圍排序，box_start並且box_end從不重疊。

如第一行所示，有一個盒子可以存放程式碼從 1 到 3 的項目。例如，盒子 A 中的物品將是“a”和“b”。

box-items 分組由著色表示，具有相同顏色的行表示它們是一個組。

還有沒有指定框的項目（框標籤為空），如項目’c’、‘f’、‘g’、‘h’。

如果可能的話，我需要編寫一個查詢，為沒有盒子的項目創建臨時盒子，並將連續的項目分組到同一個盒子中，如下所示：

如圖所示，項目“c”及其對應的框被標記為“4-4”，之前未分配的項目“f”、“g”、“h”現在被分組在標記為“8-10”的同一個框下’ 對應於 ‘min( box_startof f,g,h)-max( box_endof f,g,h)’

我不確定如何在 SQLite 中執行此操作。我考慮過對 CTE 使用某種遞歸查詢，但不知道該怎麼做。

經過一些工作，我有以下查詢：

select
   min(box_start) as box_start,
   box_end,
   box_label,
   is_box,
   item_code,
   item
from
   (
       select
           box_start,
           box_end,
           box_label,
           is_box,
           item_code,
           item
       from
           table1
       where
           box_label is not null

       union all

       select
           table1.box_start as box_start,
           table1.box_end as box_end,
           intervals.A || '-' || intervals.B as box_label,
           table1.is_box as is_box,
           table1.item_code as item_code,
           table1.item as item
       from
           (
               select
                   box_start,
                   box_end,
                   box_label,
                   is_box,
                   A,
                   B,
                   max(max_interval_size) as max_interval_size
               from
                   (
                       select
                           box_start,
                           box_end,
                           box_label,
                           is_box,
                           A,
                           B,
                           max(interval_size) as max_interval_size
                       from
                           (
                               select
                                 fixed_table.box_start as box_start,
                                 fixed_table.box_end as box_end,
                                 fixed_table.box_label as box_label,
                                 fixed_table.is_box as is_box,
                                 fixed_table.box_start as A,
                                 windowed_table.box_end as B,
                                 (windowed_table.box_end - fixed_table.box_start) as interval_size
                               from
                                 table1 fixed_table
                                 join table1 windowed_table on
                                   fixed_table.box_start <= windowed_table.box_end
                               where 
                                 interval_size >= 0
                                 and fixed_table.box_label is null
                                 and windowed_table.box_label is null
                                 and fixed_table.is_box = 'FALSE'
                                 and windowed_table.is_box = 'FALSE'

                               except

                               select
                                 without_a_box.*
                               from
                                   (
                                       select
                                         fixed_table.box_start as box_start,
                                         fixed_table.box_end as box_end,
                                         fixed_table.box_label as box_label,
                                         fixed_table.is_box as is_box,
                                         fixed_table.box_start as A,
                                         windowed_table.box_end as B,
                                         (windowed_table.box_end - fixed_table.box_start) as interval_size
                                       from
                                         table1 fixed_table
                                         join table1 windowed_table on
                                           fixed_table.box_start <= windowed_table.box_end
                                       where 
                                         interval_size >= 0
                                         and fixed_table.box_label is null
                                         and windowed_table.box_label is null
                                         and fixed_table.is_box = 'FALSE'
                                         and windowed_table.is_box = 'FALSE'
                                   ) as without_a_box
                                   ,
                                   (
                                       select distinct
                                           with_box.box_start as start_with_box
                                        from
                                           table1 with_box
                                        where
                                           with_box.is_box = 'FALSE'
                                           and with_box.box_label is not null
                                   ) as items_inside_a_box
                               where
                                   items_inside_a_box.start_with_box > without_a_box.A
                                   and items_inside_a_box.start_with_box < without_a_box.B
                           ) as without_intervals_that_intersect_boxed_items
                       group by
                           A
                   ) as final
               group by
                   B
           ) as intervals
           join table1 on
               table1.box_start >= intervals.A
               and table1.box_end <= intervals.B
               and table1.box_label is null
   )
group by
   box_label,
   is_box,
   item_code,
   item
order by
   box_start,
   item_code

SQL小提琴：http ://www.sqlfiddle.com/#!7/4a643e/142

即使它似乎完成了它的工作，我也不確定在所有情況下都是正確的，如果它不會成為性能瓶頸。

我希望有人有更好的解決方案

引用自：https://dba.stackexchange.com/questions/211053