计算tick区间内每个tick的平均ltv值并按tick分组

问题背景 #

现有ltv表结构及数据如下：

需要将ltv_usdc和ltv_eth的总值平均分配到对应tick_lower到tick_upper区间内的每个tick上，最终生成按tick分组的结果表，每个tick的数值计算公式为：

• ltv_usdc_per_tick = ltv_usdc / (tick_upper - tick_lower)
• ltv_eth_per_tick = ltv_eth / (tick_upper - tick_lower)

实现方案 #

1. PostgreSQL版本 #

利用PostgreSQL内置的generate_series函数直接生成区间内的所有tick值：

WITH expanded_ticks AS (
    SELECT
        generate_series(tick_lower, tick_upper) AS tick,
        ltv_usdc / (tick_upper - tick_lower) AS ltv_usdc_per_tick,
        ltv_eth / (tick_upper - tick_lower) AS ltv_eth_per_tick
    FROM ltv
)
SELECT
    tick,
    SUM(ltv_usdc_per_tick) AS ltv_usdc,
    SUM(ltv_eth_per_tick) AS ltv_eth
FROM expanded_ticks
GROUP BY tick
ORDER BY tick;

2. MySQL版本 #

MySQL无内置序列生成函数，使用递归CTE生成覆盖所有tick范围的序列，再关联原表计算：

WITH RECURSIVE numbers AS (
    SELECT MIN(tick_lower) AS n FROM ltv
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < (SELECT MAX(tick_upper) FROM ltv)
),
expanded_ticks AS (
    SELECT
        numbers.n AS tick,
        l.ltv_usdc / (l.tick_upper - l.tick_lower) AS ltv_usdc_per_tick,
        l.ltv_eth / (l.tick_upper - l.tick_lower) AS ltv_eth_per_tick
    FROM numbers
    JOIN ltv ON numbers.n BETWEEN l.tick_lower AND l.tick_upper
)
SELECT
    tick,
    SUM(ltv_usdc_per_tick) AS ltv_usdc,
    SUM(ltv_eth_per_tick) AS ltv_eth
FROM expanded_ticks
GROUP BY tick
ORDER BY tick;

逻辑说明 #

• 生成覆盖所有目标tick的序列：通过generate_series或递归CTE生成每个区间内的所有tick值
• 计算单tick数值：将每个区间的总LTV值除以区间内的tick数量（tick_upper - tick_lower），得到每个tick对应的数值
• 分组聚合：按tick分组求和，处理同一个tick属于多个区间的重叠场景（比如示例中204800-204880与204620-205420存在重叠，同一个tick会累加多个区间的分配值）

内容的提问来源于stack exchange，提问作者Ivan Roptanov