我收到以下错误，第 25：8 行：无法解析列'flag1'

Question

我在presto中编写了以下查询，它给出了错误：第25:8行：无法解析列'flag1'。必须包含标志条件。我在redshift上运行了类似的查询，没有任何问题。

select dt,CASE WHEN date_diff ('day',fod,dt) <= 28 then 'New' ELSE 'Old'
END AS flag1,COUNT(us.user_id) AS users,SUM(views) AS o_views,SUM(orders) AS orderss
FROM
(
(select dt,user_id,portfolio_views as views ,portfolio_orders as orders
FROM gold.user_daily_osv
) AS us
JOIN
(select user_id,CAST(first_order_ts as DATE) as fod
from gold.prism__user_master_bi
) as um
ON
us.user_id=um.user_id)
WHERE flag1 = 'New'
group by 1,2
order by 1 desc

Answer 1

考虑将WHERE flag1 = 'New'更改为WHERE date_diff ('day',fod,dt) <= 28

SELECT
dt,
CASE 
WHEN date_diff ('day',fod,dt) <= 28 then 'New' 
ELSE 'Old' 
END AS flag1,
COUNT(us.user_id) AS users,
SUM(views) AS o_views,
SUM(orders) AS orderss 
FROM ( 
(
select 
dt,
user_id,
portfolio_views as views ,
portfolio_orders as orders 
FROM 
gold.user_daily_osv 
) AS us 
JOIN (
select 
user_id,
CAST(first_order_ts as DATE) as fod 
from gold.prism__user_master_bi 
) as um ON us.user_id=um.user_id
) 
WHERE date_diff ('day',fod,dt) <= 28 
group by 1,2 
order by 1 desc

Answer 2

为什么还要麻烦定义标志？你可以使用：

SELECT udo.dt, 'New' as flag1,
COUNT(*) AS users,
SUM(udo.portfolio_views) AS o_views,
SUM(udo.portfolio_orders) AS orderss 
FROM gold.user_daily_osv udo JOIN
gold.prism__user_master_bi um
ON us.user_id = um.user_id
WHERE date_diff('day', CAST(um.first_order_ts as DATE), udo.dt) <= 28 
GROUP BY 1
ORDER BY 1 DESC;

注：

• 使用表别名，使查询更易于编写和读取
• 不需要子查询
• 与使用date_diff()相比，我更喜欢直接进行日期比较，但我在代码中保留了date_diff()
• COUNT()在没有列的情况下工作。不需要计算列的非NULL值的数量。只需使用COUNT(*)。或者，如果您愿意，COUNT(1)