时间戳

首页 > 解决方案 > 按天求和和分组输出 SQL

sql - 按天求和和分组输出 SQL

问题描述

我无法通过别名 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

标签： sqlgoogle-bigquery

解决方案

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

-- 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

将您的逻辑分解为步骤/块通常会使事情变得更容易。

推荐阅读