where语句中的sql条件搜索

eivnm1vs 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(454)

我有一张每小时更新一次的table。有时它可能会因为任何原因错过一个小时。我想每天凌晨4点数记录，如果没有记录，就看下一个小时（凌晨5点）。如果两个记录都不存在，我也希望它返回0。
以下是我目前掌握的情况：

SELECT 
    DATE(last_update) as dt,
    COUNT(DISTINCT "number") as redlit
FROM mytable
WHERE 
    status = 'foo' AND 
    defective = 'yes' AND
    last_update >= '2020-01-01' AND
CASE WHEN (DATE_PART('hour',last_update) = 4) IS NULL THEN (DATE_PART('hour',last_update) = 5)
ELSE (DATE_PART('hour',last_update) = 4) END
GROUP BY 1
ORDER BY dt;

这个 CASE WHEN 声明似乎只看凌晨4点。我不知道如何在凌晨4点不存在的情况下让它看凌晨5点。如果两者都是，我不知道如何返回0 NULL .
样品：

dt       redlit datepart
2020-03-19  21  4.0
2020-03-20  14  4.0
2020-03-22  16  4.0
2020-03-23  45  4.0

期望结果：

dt       redlit datepart
2020-03-19  21  4.0
2020-03-20  14  4.0
2020-03-21  20  5.0
2020-03-22  16  4.0
2020-03-23  45  4.0
2020-03-24  0   NULL

sql amazon-redshift

来源：https://stackoverflow.com/questions/62273330/conditional-search-in-where-statement

1条答案

按热度按时间

bbuxkriu1#

我不太熟悉redshift的语法，也没有办法测试这段代码。但这应该管用。这段代码并不优雅，可能需要一些调整

Select m.dt,
    Case When dp = 4 then m4.redlit 
        When  dp = 5 then m5.redlit
        Else 0 End as  redlit
 from
(
    SELECT 
        DATE(last_update) as dt,
        0 as dp,
        COUNT(DISTINCT "number") as redlit
    FROM mytable
    WHERE 
        status = 'foo' AND dt
        defective = 'yes' AND
        last_update >= '2020-01-01' AND
    GROUP BY 1,2
) m
left join
(
    SELECT 
        DATE(last_update) as dt,
        4 as dp,
        COUNT(DISTINCT "number") as redlit
    FROM mytable
    WHERE 
        status = 'foo' AND 
        defective = 'yes' AND
        last_update >= '2020-01-01' AND
        DATE_PART('hour',last_update) = 4
    GROUP BY 1,2
) m4 on m.dt = m4.dt 
left join
(
    SELECT 
        DATE(last_update) as dt,
        5 as dp,
        COUNT(DISTINCT "number") as redlit
    FROM mytable
    WHERE 
        status = 'foo' AND 
        defective = 'yes' AND
        last_update >= '2020-01-01' AND
        DATE_PART('hour',last_update) = 4
    GROUP BY 1,2
) m5 on m.dt = m5.dt

赞(0）回复(0）举报 2021-08-09

我来回答

where语句中的sql条件搜索

1条答案

相关问题

热门标签

最新问答