带日期筛选语句的sql

t98cgbkg 于 2021-06-26 发布在 Impala

关注(0)|答案(2)|浏览(360)

我有几年的价格数据，我需要从中提取特定日期的数据：一年前，一个季度前，一个月前，两周前。我使用ApacheImpalaSQL
我尝试使用with语句来创建筛选日的子集。因为一年前的日期可能是周末或假日，所以我从（-370-365天前）

WITH yearpast AS (
    SELECT max(quote_date_time) as yearago
    FROM quotes_raw
    WHERE quote_date_time >= adddate(now(), -370) and quote_date_time <= adddate(now(), -365)),

monthpast AS (
    SELECT max(quote_date_time) as monthago
    FROM quotes_raw
    WHERE quote_date_time >= adddate(now(), -33) and quote_date_time <= adddate(now(), -30)
    )

SELECT close_px FROM quotes_raw
    WHERE quote_date_time IN (yearpast.yeargo, monthpast.monthago)

我希望select语句使用yearpast的quote\u date\u time作为过滤器，但是我得到了一个错误。

sql impala common-table-expression

来源：https://stackoverflow.com/questions/57466734/sql-with-statements-for-date-filtering

2条答案

按热度按时间

vngu2lb81#

就用这个怎么样 order by 以及 limit ?

select qr.*from quotes_raw qr
where quote_date_time >= adddate(now(), -370)
order by quote_date_time
limit 1;

赞(0）回复(0）举报 2021-06-26

btqmn9zl2#

您需要咨询cte：

WITH yearpast AS (
    SELECT max(quote_date_time)
    FROM quotes_raw
    WHERE quote_date_time >= adddate(now(), -370) and quote_date_time <= adddate(now(), -365)
)
SELECT close_px 
FROM quotes_raw
WHERE quote_date_time IN (SELECT yearpast.quote_date_time FROM yearpast);

编辑：

WITH yearpast AS (
    SELECT max(quote_date_time) as yearago
    FROM quotes_raw
    WHERE quote_date_time>=adddate(now(),-370) and quote_date_time <= adddate(now(), -365)),

monthpast AS (
    SELECT max(quote_date_time) as monthago
    FROM quotes_raw
    WHERE quote_date_time >= adddate(now(), -33) and quote_date_time <= adddate(now(), -30)
    )

SELECT close_px 
FROM quotes_raw
WHERE quote_date_time IN (SELECT yearpast.yeargo FROM yearpast
                          UNION ALL SELECT monthpast.monthago FROM monthpast)

赞(0）回复(0）举报 2021-06-26

我来回答

带日期筛选语句的sql

2条答案

相关问题

热门标签

最新问答