← 返回知识库← 上一题下一题 →
SQL (ANSI)编程SQL开发人员/数据工程师

描述ANSI SQL窗口函数技术，以隔离最小的有序记录子集，其累积贡献占总额的80%。

用 Hintsage AI 助手通过面试

问题的答案

帕累托原则源于维尔弗雷多·帕累托对意大利土地所有权的观察，后来成为质量控制和库存管理的基石，这得益于约瑟夫·朱兰的研究。在关系数据库中，这转化为需要进行ABC分析，分析师必须识别推动大部分商业价值的关键少数记录，而不依赖外部统计工具。

这个问题需要计算一个运行百分比，该百分比对比降序排列的指标和绝对总值，然后在80%的阈值处截断。因为ANSI SQL操作的是集合而不是迭代游标，窗口函数提供了描述性机制。解决方案使用了在整个结果集上分区的累积和，按降序排列的值，然后在相同的行上下文中除以总额，以得出一个百分比排名。

关键的是，框架规范ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW确保了一行一行的确定性累积。如果需要严格处理平局—所有共享边界值的记录必须作为一个单位包含或排除—RANGE将替代ROWS。最终过滤必须发生在外部查询中，因为窗口函数的逻辑计算在WHERE子句之后。

WITH ranked_products AS (
    SELECT 
        product_id,
        product_name,
        annual_revenue,
        SUM(annual_revenue) OVER (
            ORDER BY annual_revenue DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_revenue,
        SUM(annual_revenue) OVER () AS total_revenue
    FROM inventory
),
pareto_subset AS (
    SELECT 
        product_id,
        product_name,
        annual_revenue,
        CAST(cumulative_revenue AS DECIMAL) / total_revenue AS cumulative_pct
    FROM ranked_products
)
SELECT product_id, product_name, annual_revenue, cumulative_pct
FROM pareto_subset
WHERE cumulative_pct <= 0.80;

生活中的情况

一家国家级电子零售商在季度库存审计中面临不断上升的成本，需要隔离代表库存总资本80%（40000件商品总计5000万美元）的高价值SKU，以优先进行周期性盘点。

解决方案1：电子表格提取涉及分析师将CSV文件导出到Excel中，按单价排序，并手动累计直到达到阈值。优点是不需要开发时间，缺点是处理大型数据集时可能导致应用程序崩溃，需要每小时进行重新计算，并阻止与仓库管理系统的实时集成。

解决方案2：应用层计算使用Python脚本流式传输行并维护运行累计器。优点是提供灵活逻辑和易于调试，缺点是传输数百万行时引入显著的网络延迟，单线程执行阻塞分析仪表板，以及客户端机器上的内存限制。

解决方案3：ANSI SQL基于集的方案直接在PostgreSQL仓库中实现窗口函数查询。优点包括毫秒级延迟，消除了数据移动，并在每晚更新时自动刷新，缺点是维护需要高级SQL知识。

所选方案和结果： 方案3作为视图部署，揭示仅12%的SKU占据了80%的价值。审计范围减少88%，每季度节省340个劳动小时，同时保持物料价值的完全覆盖。

候选人常常忽略的内容

当存在重复值时，选择ROWS和RANGE框架规范如何影响80%阈值？

RANGE将具有相同ORDER BY值的同级行视为一个组；如果80%边界落在平局内，RANGE将包括整个组，可能会超过80%。ROWS根据物理偏移处理，而不考虑平局，这可能会拆分一个逻辑业务单元。候选人常常忽视ANSI SQL允许显式调整此行为；对于财务报告，RANGE确保一致的期间不被拆分，而ROWS则为不同项目提供更细的粒度。

为什么必须在派生表或CTE中执行累积百分比计算，而不是直接在WHERE子句中？

窗口函数在SELECT阶段进行逻辑计算，而该阶段在WHERE子句过滤行之后发生。试图在WHERE中直接过滤cumulative_revenue / total_revenue <= 0.8会导致语法错误，因为窗口结果尚未物化。候选人往往在理解ANSI SQL的逻辑处理顺序时遇到困难：FROM → WHERE → GROUP BY → HAVING → WINDOW → SELECT → ORDER BY。该解决方案需要嵌套以在内部查询中计算窗口函数，然后在外部查询中过滤结果列。

如果库存表包含数十亿行，估计80%的子集非常小，您将如何优化此查询？

候选人常常忽略Top-N优化模式。可以使用带有DENSE_RANK()或NTILE()的子查询进行初步过滤，而不是在整个表上计算窗口函数，从而将窗口计算限制在最重要的候选者中。或者，如果分析按类别分段，利用PARTITION BY可以防止全表扫描。理解窗口函数强制排序操作，并且在收入列上按降序索引可以消除排序成本，对于规模化至关重要。