计算运行总数,保持每月不变

首先,在distinct上获取运行和 monthdates 然后把它们放在你的桌子上 月初 .

SELECT t2.monthdates,
       x2.sum
       FROM mytable t2
            INNER JOIN (SELECT x1.monthdates,
                               sum(10) OVER (ORDER BY x1.monthdates) sum
                               FROM (SELECT DISTINCT
                                            t1.monthdates
                                            FROM mytable t1) x1) x2
                       ON x2.monthdates = t2.monthdates
       ORDER BY t2.monthdates;

你可以更容易地通过使用 dense_rank() 乘以 10 但是没有 sum() .

SELECT t1.monthdates,
       dense_rank() OVER (ORDER BY t1.monthdates) * 10 sum
       FROM mytable t1
       ORDER BY t1.monthdates;

db<>fiddle