SQL实现抽样分层合并：将频数不足的分层3合并至上层

问题背景 #

在抽样过程中，计算各分层（strata）方差时每个分层至少需要2个样本。现需将频数为1的分层3合并至其上层分层（默认规则：频数不足的分层合并至上层，必要时首个分层可合并至最后一个），请问：

1. 能否用SQL实现该分层合并操作？
2. 如何将第一张表转换为第二张表？

我猜测可借助analytic functions，结合类似ROWS BETWEEN 1 AND PRECEDING AND 1 FOLLOWING的语法与IF来识别样本数不足2的行，但遇到了问题。

问题1：能否用SQL实现？ #

完全可以用SQL实现这类分层合并操作，窗口函数（analytic functions）是处理这类分层关联逻辑的常规方案，能高效实现规则内的合并判断与分组。

问题2：具体实现思路（以通用SQL方言为例） #

假设原始表为strata_data，包含strata_id（分层ID）、frequency（频数）两个核心字段，可按以下步骤实现转换：

1. 标记需合并的分层：用LAG()和LEAD()窗口函数获取当前分层的上下层ID，结合频数判断是否需要合并。
2. 确定合并目标：遵循“频数不足则合并至上层，首个分层特殊情况合并至最后一层”的规则，为每个分层指定最终的分组ID。
3. 聚合合并结果：按分组ID汇总频数，得到合并后的分层数据。

示例代码（以MySQL为例）：

-- 第一步：标记分层并获取上下层信息
WITH marked_strata AS (
    SELECT
        strata_id,
        frequency,
        CASE WHEN frequency < 2 THEN 1 ELSE 0 END AS need_merge,
        -- 上层ID：首个分层取最后一个分层ID，否则取前一个分层ID
        COALESCE(LAG(strata_id) OVER (ORDER BY strata_id),
                 (SELECT strata_id FROM strata_data ORDER BY strata_id DESC LIMIT 1)) AS upper_strata
    FROM strata_data
),
-- 第二步：指定合并后的分组ID
grouped_strata AS (
    SELECT
        CASE WHEN need_merge = 1 THEN upper_strata ELSE strata_id END AS target_strata_id,
        frequency
    FROM marked_strata
)
-- 第三步：聚合得到最终结果
SELECT
    target_strata_id AS strata_id,
    SUM(frequency) AS total_frequency
FROM grouped_strata
GROUP BY target_strata_id
ORDER BY target_strata_id;

关于你提到的窗口语法问题 #

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING是窗口框架范围的定义语法，多用于聚合前后行的统计值，但在这个场景中，我们需要的是获取前后行的具体分层ID，LAG()和LEAD()函数更直接，专门用于提取前后行的字段值，比手动定义窗口框架更简洁高效。

如果存在连续多个频数不足的分层（比如分层2、3频数都为1），可以调整逻辑，用累计标记的方式将连续需合并的分层归到同一个上层分组，避免重复合并。

内容的提问来源于stack exchange，提问作者Fernando Mauricio Rivera Vega