利用Window Function解关联示例

示例1：以TPC-H中的Q2为例，该查询希望查找特定区域，对于指定类型和大小的零件，能够提供最小批发价的供应商。该查询在社区MySQL中执行需要先执行外层查询获取指定类型和大小的零件供应商的信息，然后对获取的每一条数据通过子查询计算该零件在指定区域中所有供应商最小的批发价，然后判断获取的供应商批发价和子查询获取的最小批发价是否一致。

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
 s_address, s_phone, s_comment
FROM part, supplier, partsupp, nation, region
WHERE p_partkey = ps_partkey
   AND s_suppkey = ps_suppkey
   AND p_size = 30
   AND p_type LIKE '%STEEL'
   AND s_nationkey = n_nationkey
   AND n_regionkey = r_regionkey
   AND r_name = 'ASIA'
   AND ps_supplycost = (
       SELECT MIN(ps_supplycost)
       FROM partsupp, supplier, nation, region
       WHERE p_partkey = ps_partkey
           AND s_suppkey = ps_suppkey
           AND s_nationkey = n_nationkey
           AND n_regionkey = r_regionkey
           AND r_name = 'ASIA'
   )
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

利用Window function按照指定的分组方式分组计算聚合函数，在原有数据行上增加分组计算的聚合结果。对于TPC-H中的Q2，可以获取特定区域，指定类型和大小零件的供应商同时按照零件信息进行分组计算获得最小批发价。然后通过比较获取的数据行中批发价和分组最小批发价比较来选择需要的数据。Q2经过查询变换后，获得以下查询的等价执行。

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
  s_address, s_phone, s_comment
FROM (
    SELECT MIN(ps_supplycost) OVER(PARTITION BY ps_partkey) as win_min,
      ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, s_address,
      s_phone, s_comment
    FROM part, partsupp, supplier, nation, region
    WHERE p_partkey = ps_partkey
      AND s_suppkey = ps_suppkey
      AND s_nationkey = n_nationkey
      AND n_regionkey = r_regionkey
      AND p_size = 30
      AND p_type LIKE '%STEEL'
      AND r_name = 'ASIA') as derived
WHERE ps_supplycost = derived.win_min
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
LIMIT 100;

示例2：

#指定满足条件的子查询利用WINDOW_FUNCTION解关联
SELECT ... FROM ... WHERE ... = (SELECT /*+UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
SELECT /*+UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)

#指定子查询不要利用WINDOW_FUNCTION解关联
SELECT ... FROM ... WHERE ... = (SELECT /*+NO_UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
SELECT /*+NO_UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)

相关文档
利用Window Function解关联