按DAY SQL对输出进行求和和和分组

Question

我不能通过别名Gross_Demand和Units引用。

,(CASE WHEN CONCAT(',',post_event_list,',') LIKE '%,1,%' THEN SPLIT(item, ";")[safe_offset(3)] END) Gross_Demand
,(CASE WHEN CONCAT(',',post_event_list,',') LIKE '%,1,%' THEN SPLIT(item, ";")[safe_offset(2)] END) Units

Answer 1

故意不给你一个具体的答案，但我会这样构建你的查询：

-- Declare your variables
DECLARE StartDate...
with hits as (
-- select what you need from your hits table, filter and unnest here
),
products as (
-- select and filter what you need from your products table here
),
joined as (
-- join the two sources together and do any additional filtering
select 
date, visits, orders, gross_demand, units, etc...
from hits
join products
),
calcs as (
select
*,  
gross_demand*units as revenue,
orders/visits * 100 as conversion_pct,
gross_demand*units/visits as revenue_per_visit
from joined
)
select * from calcs

把你的逻辑分解成步骤/块通常会让事情变得更容易。