时间戳

首页 > 解决方案 > 如果今天没有数据，则查询上一行数据

sql - 如果今天没有数据，则查询上一行数据

问题描述

语境：

我正在查询每日外汇汇率。目前，我们通过仅在一天中的特定时间开始运行的 API 获取信息。但是，我希望能够在数据可用之前显示汇率。

因此，例如，假设 API 在当地时间早上 7 点抓取数据，这意味着如果我想在早上 7 点之前查询外汇汇率，则由于后端脚本尚未开始将数据加载到数据库中，因此不会显示任何内容。由于我无法访问后端，因此我想使用一种解决方法，使用 SQL 来检索昨天的汇率，直到今天的汇率可用。

下面是表格的外观：

Table: forex_daily_rate
ID              DATE            SOURCE  TARGET      RATE
20210531AUDUSD  2021-05-31      AUD     USD         0.772797527
20210531CADUSD  2021-05-31      CAD     USD         0.8280887711
20210531EURUSD  2021-05-31      EUR     USD         1.219066195
20210531GBPUSD  2021-05-31      GBP     USD         1.418238548
20210530AUDUSD  2021-05-30      AUD     USD         0.7714858818
20210530CADUSD  2021-05-30      CAD     USD         0.8287750704
20210530EURUSD  2021-05-30      EUR     USD         1.219363492
20210530GBPUSD  2021-05-30      GBP     USD         1.420252805

笔记：

我试过下面的查询：

WITH _base as (
SELECT * FROM forex_daily_rates
)

SELECT B.id, B.date, B.source, B.target,
CASE WHEN max(B.date) != current_date THEN lag(rate) OVER (PARTITION BY target,source ORDER BY B.date DESC)
    ELSE rate
END as rate
FROM _base B

即使没有语法错误，它仍然不显示 2021-06-01 的速率（当然假设 2021-06-01 的数据尚未加载到数据库中）。

鉴于 2021-06-01 尚不可用，预期的最终结果应该是：

ID              DATE            SOURCE  TARGET      RATE
20210601AUDUSD  2021-06-01      AUD     USD         0.772797527
20210601CADUSD  2021-06-01      CAD     USD         0.8280887711
20210601EURUSD  2021-06-01      EUR     USD         1.219066195
20210601GBPUSD  2021-06-01      GBP     USD         1.418238548
20210531AUDUSD  2021-05-31      AUD     USD         0.772797527
20210531CADUSD  2021-05-31      CAD     USD         0.8280887711
20210531EURUSD  2021-05-31      EUR     USD         1.219066195
20210531GBPUSD  2021-05-31      GBP     USD         1.418238548

我想可能是因为表中还不存在日期，因此查询也没有任何附加值。我尝试创建一个临时表，该表生成从 2017-01-01 开始的日期列表，称为calendar，但仍然无法正常工作。

WITH calendar AS (
  SELECT date_trunc('day', dd)::date AS date
     FROM generate_series ('2017-01-01'::TIMESTAMP, CURRENT_DATE , '1 day'::interval) dd
    ),
    
    _base as (
    SELECT * FROM forex_daily_rates
    )

SELECT B.id, C.date, B.source, B.target,
CASE WHEN max(C.date) != current_date THEN lag(rate) OVER (PARTITION BY target,source ORDER BY C.date DESC)
    ELSE rate
END as rate
FROM calendar C
LEFT JOIN _base B ON C.date = B.date
GROUP BY 1,3,4, C.date
ORDER BY 2 DESC

将不胜感激帮助，无论是在 Postgres 和 Snowflake 语法都很好！非常感谢！

标签： sqlpostgresqlsnowflake-cloud-data-platform

解决方案

我认为以下应该有效。我尽量避免使用 a union，这样如果你有一张大桌子，它的性能会更好。

使用示例数据创建表：

create or replace transient table test_table as (
    select
        column1 as id,
        column2 as date,
        column3 as source,
        column4 as target,
        column5 as rate
    from (values ('20210602AUDUSD', '2021-06-02'::date, 'AUD', 'USD', 0.11111),
                 ('20210602CADUSD', '2021-06-02'::date, 'CAD', 'USD', 0.11111),
                 ('20210601AUDUSD', '2021-06-01'::date, 'AUD', 'USD', 0.22222),
                 ('20210601CADUSD', '2021-06-01'::date, 'CAD', 'USD', 0.22222),
                 ('20210601EURUSD', '2021-06-01'::date, 'EUR', 'USD', 0.22222),
                 ('20210601GBPUSD', '2021-06-01'::date, 'GBP', 'USD', 0.22222)
             )
);

询问

with latest_rates as (
    select
        to_char(current_date, 'YYYYMMDD') || source || target as id,
        current_date                                          as date,
        source,
        target,
        rate
    from test_table
        qualify row_number() over (partition by source, target order by date desc) = 1
)
select * from latest_rates
union all
select * from test_table where id not in (select id from latest_rates)
;

结果

+--------------+----------+------+------+-------+
|ID            |DATE      |SOURCE|TARGET|RATE   |
+--------------+----------+------+------+-------+
|20210602AUDUSD|2021-06-02|AUD   |USD   |0.11111|
|20210602CADUSD|2021-06-02|CAD   |USD   |0.11111|
|20210602EURUSD|2021-06-02|EUR   |USD   |0.22222|
|20210602GBPUSD|2021-06-02|GBP   |USD   |0.22222|
|20210601AUDUSD|2021-06-01|AUD   |USD   |0.22222|
|20210601CADUSD|2021-06-01|CAD   |USD   |0.22222|
|20210601EURUSD|2021-06-01|EUR   |USD   |0.22222|
|20210601GBPUSD|2021-06-01|GBP   |USD   |0.22222|
+--------------+----------+------+------+-------+

推荐阅读