PL/SQL 统计频次表中位数

-- 频次统计表
CREATE TABLE T_SCORE (
    NUM INTEGER, -- 数值
    FRQ INTEGER  -- 出现次数
);

-- 计算频次统计表中位数的函数
CREATE OR REPLACE FUNCTION F_MEDIAN (p_table_name IN VARCHAR2)
RETURN NUMBER
IS
  v_count NUMBER;
BEGIN
    WITH MED AS (
        SELECT NUM,
               FRQ,
               SUM(FRQ) OVER(ORDER BY NUM RANGE UNBOUNDED PRECEDING) FRQ_CUMSUM,
                    -- 频率累加滑窗
               SUM(FRQ) OVER() TOT_CNT,
                    -- 固定值，样本总数
               ROW_NUMBER() OVER(ORDER BY NUM) RN
                    -- 按数值大小计数，为后续计算
        FROM t_score
    ),
    -- MED_FILTER：初筛一下，判断中位数落在哪个区间
    MED_FILTER AS (
        SELECT *
          FROM MED
         WHERE FRQ_CUMSUM >= FLOOR((TOT_CNT + 1) / 2)
    )
    --SELECT * FROM MED;
    SELECT CASE WHEN 2 * FRQ_CUMSUM > TOT_CNT THEN NUM
                    -- 包含两种情况：奇数样本数以及落在区间内
                ELSE (   SELECT (M1.NUM + M2.NUM) / 2 "MEDIAN"
                           FROM MED M1
                      LEFT JOIN MED M2
                             ON M1.RN + 1 = M2.RN
                          WHERE M1.FRQ_CUMSUM = MED_FILTER.FRQ_CUMSUM
                     )
                    -- 落在区间之间，需要到 MED 表找原始记录
            END AS "MEDIAN" INTO v_count
      FROM MED_FILTER
     WHERE FRQ_CUMSUM = (SELECT MIN(FRQ_CUMSUM) FROM MED_FILTER);
  -- 返回查询到的记录数
  RETURN v_count;
END;

-- 模拟 n 从 1 到 2^30，计算 n / m_n
DECLARE
  i NUMBER := 1;
  m NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE NEWBIE.T_SCORE';
  WHILE i <= POWER(2, 30) LOOP
    INSERT INTO T_SCORE VALUES (i, i);
    IF BITAND(i, i - 1) = 0 THEN
        m := F_MEDIAN('T_SOCRE');
        -- 为实时输出结果，这里弄了个存储过程
        TO_DBG_FILE('F_MEDIAN.log',
                    LPAD(i, 10) || ' => ' ||
                    RPAD(m, 10) || ' (' ||
                    TO_CHAR(i/m, 'FM999999999999999.000000000000000') || ')');
    END IF;
    i := i + 1;
  END LOOP;
END;

-- 实时输出日志到文件
CREATE OR REPLACE PROCEDURE TO_DBG_FILE(p_fname VARCHAR2, p_log VARCHAR2) AS
  l_file UTL_FILE.file_type;
BEGIN
  -- 需提前建好 LOG_DIR 这个 DIRECTORY
  l_file := UTL_FILE.fopen('LOG_DIR', p_fname, 'A');
  UTL_FILE.put_line(l_file, p_log);
  UTL_FILE.fflush(l_file);
  UTL_FILE.fclose(l_file);
END TO_DBG_FILE;

------------------- 实验结果-------------------
---         1 => 1          (1.000000000000000)
---         2 => 2          (1.000000000000000)
---         4 => 3          (1.333333333333333)
---         8 => 6          (1.333333333333333)
---        16 => 12         (1.333333333333333)
---        32 => 23         (1.391304347826087)
---        64 => 46         (1.391304347826087)
---       128 => 91         (1.406593406593407)
---       256 => 181        (1.414364640883978)
---       512 => 362        (1.414364640883978)
---      1024 => 724        (1.414364640883978)
---      2048 => 1449       (1.413388543823326)
---      4096 => 2897       (1.413876423886779)
---      8192 => 5793       (1.414120490246850)
---     16384 => 11586      (1.414120490246850)
---     32768 => 23171      (1.414181520003453)
---     65536 => 46341      (1.414212036857211)
---    131072 => 92682      (1.414212036857211)
---    262144 => 185364     (1.414212036857211)
---    524288 => 370728     (1.414212036857211)
---   1048576 => 741456     (1.414212036857211)
---   2097152 => 1482911    (1.414212990530113)
---   4194304 => 2965821    (1.414213467367046)
---   8388608 => 5931642    (1.414213467367046)
---  16777216 => 11863284   (1.414213467367046)
---  33554432 => 23726567   (1.414213526971685)
-----------------------------------------------