又是一个填日期空白的SQL谜题

我正在使用Vertica，这使我无法使用CROSS APPLY，不幸的是。很明显，在Vertica中没有cte这种东西。

这是我得到的:

t:
    day    | id | metric | d_metric
-----------+----+--------+----------
2011-12-01 |  1 | 10     | 10
2011-12-03 |  1 | 12     | 2
2011-12-04 |  1 | 15     | 3

注意，在第一天，delta等于度量值。我想填入空格，像这样:

t_fill:
    day    | id | metric | d_metric
-----------+----+--------+----------
2011-12-01 |  1 | 10     | 10
2011-12-02 |  1 | 10     | 0 -- a delta of 0
2011-12-03 |  1 | 12     | 2
2011-12-04 |  1 | 15     | 3

我已经想到了一个每天都这样做的方法，但我真正想要的是一个一次性解决方案。

我认为我可以得到一些工作与LAST_VALUE，但我不能提出正确的JOIN语句，将让我正确地分区和排序每个id的逐日历史。

编辑:假设我有一个这样的表:

calendar:
    day 
------------
2011-01-01
2011-01-02
   ...

可以与连接有关。我的意图是维护日历中的日期范围以匹配t中的日期范围。

编辑:关于我正在寻找的东西的一些注意事项，只是为了具体:

在生成t_fill时，我想准确地覆盖t中的日期范围，以及中间缺失的任何日期。因此，正确的t_fill将在与t相同的日期开始和结束。t_fill有两个属性:

1)一旦id出现在某个日期，它将始终为每个以后的日期保留一行。这是原始问题中隐含的空白填补。

2)如果在某个日期之后没有id的行再次出现，t_fill解决方案应该愉快地生成从最后一个数据点的日期到t结束日期具有相同度量值(和0 delta)的行。

解决方案可能回填较早的日期，直到t中日期范围的开始。也就是说，对于出现在t中的第一个日期之后的任何id， t中的第一个日期与该id的第一个日期之间的行将用metric=0和d_metric=0填充。我不喜欢这种解决方案，因为它对进入系统的每个id都有更高的增长因子。但我可以很容易地处理它，通过选择到一个新的表中只有行，其中metric!

这是Jonathan Leffler提出的，但是使用老式的低级的 SQL(没有花哨的CTE或窗口函数或聚合子查询):

SET search_path='tmp'
DROP TABLE ttable CASCADE;
CREATE TABLE ttable
        ( zday date NOT NULL
        , id INTEGER NOT NULL
        , metric INTEGER NOT NULL
        , d_metric INTEGER NOT NULL
        , PRIMARY KEY (id,zday)
        );
INSERT INTO ttable(zday,id,metric,d_metric) VALUES
 ('2011-12-01',1,10,10)
,('2011-12-03',1,12,2)
,('2011-12-04',1,15,3)
        ;
DROP TABLE ctable CASCADE;
CREATE TABLE ctable
        ( zday date NOT NULL
        , PRIMARY KEY (zday)
        );
INSERT INTO ctable(zday) VALUES
 ('2011-12-01')
,('2011-12-02')
,('2011-12-03')
,('2011-12-04')
        ;
CREATE VIEW v_cte AS (
        SELECT t.zday,t.id,t.metric,t.d_metric
        FROM ttable t
        JOIN ctable c ON c.zday = t.zday
        UNION
        SELECT c.zday,t.id,t.metric, 0
        FROM ctable c, ttable t
        WHERE t.zday < c.zday
        AND NOT EXISTS ( SELECT *
                FROM ttable nx
                WHERE nx.id = t.id
                AND nx.zday = c.zday
                )
        AND NOT EXISTS ( SELECT *
                FROM ttable nx
                WHERE nx.id = t.id
                AND nx.zday < c.zday
                AND nx.zday > t.zday
                )
        )
        ;
SELECT * FROM v_cte;

结果:

    zday    | id | metric | d_metric 
------------+----+--------+----------
 2011-12-01 |  1 |     10 |       10
 2011-12-02 |  1 |     10 |        0
 2011-12-03 |  1 |     12 |        2
 2011-12-04 |  1 |     15 |        3
(4 rows)

我不是Vertica的用户，但是如果您不想使用他们对GAP填充的原生支持，那么您可以在这里找到一个更通用的仅sql的解决方案。

如果您想使用CTE之类的东西，那么使用临时表如何?从本质上讲，CTE是特定查询的视图。

根据需要，可以将临时表设置为事务作用域或会话作用域。

我仍然很想知道为什么用常数插值填充空白在这里不起作用

给定完整的日历表，这是可行的，尽管并非微不足道。如果没有日历表，将会困难得多。

你的查询需要适度精确地陈述，这通常是"如何编写查询"问题的一半。我想你要找的是:

对于日历中T所表示的最小和最大日期之间的每个日期(或其他规定范围)，
对于T表示的每个不同ID，
查找给定ID在日期或日期之前在T中最近记录的度量。

这给了你一个完整的日期列表和指标。

然后，您需要自连接该列表的两个副本，其中日期间隔一天，以形成delta。

请注意，如果某些ID值没有出现在日期范围的开始，它们将不会显示。

我相信有了这个指导，你应该可以开始了。

相关内容

最新更新

热门标签：