如何根据参数日期将行中的值按最接近的日期累加到日期列表中作为参数?

虽然计算结果与预期结果不同,因为计算使用了两个位置 1547015021 和 1547024080 最接近所提供的时间戳 1546992000 ;

以下内容可以作为基于SQLite的解决方案的基础:-

WITH
-- The supplied list of timestamps
    v (cv,dflt) AS (
        VALUES (1546905600,0),(1546992000,0),(1547078400,0)
    ),
    -- Join the two sets calculating the difference
    cte1 AS (
        SELECT *, abs(cv - timestamp) AS diff FROM roundit_base INNER JOIN v
    ),

    -- Find the closest (smallest difference) for each timestamp
    cte2 AS (
        SELECT *, min(diff) FROM cte1 GROUP BY timestamp
    )
    -- For each compartive value sum the counts allocated/assigned (timestamps) to that 
    SELECT cv,
       CASE 
         WHEN 
                (SELECT sum(cnt) FROM cte2 WHERE cv = v.cv) IS NOT NULL 
         THEN
                (SELECT sum(cnt) FROM cte2 WHERE cv = v.cv)
         ELSE 0
         END AS cnt
    FROM v;
 ;

上述结果如下:-