常见案例

根据用户的 distinct_id 查询某个用户在某天的具体行为

直接使用 distinct_id 查询即可:

SELECT * FROM events WHERE distinct_id = 'wahaha' AND date = '2015-09-10' LIMIT 100
SQL

查询每天上午 10 点至 11 点的下单用户数

使用标准的 SQL 日期函数 EXTRACT 来取出小时信息。

SELECT date, COUNT(*) FROM events 
WHERE EXTRACT(HOUR FROM time) IN (10, 11) AND event = 'SubmitOrder'
GROUP BY 1
SQL

查询一段时间内的用户下单次数分布情况

首先计算每个用户的下单次数，然后使用 CASE..WHEN 语法来分组。

SELECT 
    CASE
        WHEN c < 10 THEN '<10'
        WHEN c < 20 THEN '<20'
        WHEN c < 100 THEN '<100'
        ELSE '>100'
    END,
    COUNT(*)
FROM (
    SELECT user_id, COUNT(*) AS c FROM events
    WHERE date BETWEEN '2015-09-01' AND '2015-09-20' AND event = 'SubmitOrder'
    GROUP BY 1
)a 
GROUP BY 1
SQL

查询做了行为 A 而没有做行为 B 的用户数

使用 LEFT OUTER JOIN 计算差集。

SELECT a.user_id FROM (
  SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'BuyGold'
) a
LEFT OUTER JOIN (
SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'SaleGold'
) b
ON a.user_id = b.user_id
WHERE b.user_id IS NULL
SQL

计算用户的使用时长

使用分析函数，根据每个用户相邻的两个事件的间隔估算累计使用时长，如果两次使用间隔超出 10 分钟则不计算。

SELECT
user_id,
SUM(
  CASE WHEN
   end_time - begin_time < 600
  THEN
   end_time - begin_time
  ELSE
   0
  END
) FROM (
    SELECT
    user_id,
    EXTRACT(EPOCH FROM time) AS end_time,
    LAG(EXTRACT(EPOCH FROM time), 1, NULL) OVER (PARTITION BY user_id ORDER BY time asc) AS begin_time
    FROM events
    WHERE date='2015-5-1'
) a
GROUP BY 1
SQL

获取用户的首次行为属性

使用 first_time_value(time, 其他属性) 聚合函数来获取第一次发生某行为时的相关属性

-- 示例1：获取用户第一次发生页面浏览行为时，所在的页面 URL
SELECT user_id, first_time_value(time, $url) FROM events WHERE event = '$pageview' GROUP BY user_id

-- 示例2：获取用户第一次购买时，所购买的金额
SELECT user_id, first_time_value(time, order_amount) first_order_amount FROM events WHERE event = 'payOrder' GROUP BY user_id
SQL

不兼容语法变更

自定义查询引擎切换为直接查引擎模式，并将底层查询引擎版本升级到了 4.8，因此存在少量的不兼容语法变更。

  • events.date 字段变为 datetime 类型, 不再作为 number 类型支持进行 number 类型的计算, 例如不支持 + - * /。例如:
/* 支持的语法 */
SELECT * FROM events WHERE date = '2021-03-01' + interval 1 day

/* 不再支持的语法 */
SELECT * FROM events WHERE date - 1 = '2021-03-01'
SQL
  • 使用 events.date 字段进行两表 Join 时，因为 date 字段的类型变更，根据 Join 的数据量条数，会带来不同程度的查询性能上的下降。这种写法暂时没有办法规避，我们会在后续的版本中考虑重写这个条件，避免每次计算函数。建议在性能比较敏感的场景之下，尽量从业务场景上避免 events.date 字段参与 Join 运算。例如：
/* 有性能损失的用法 */
SELECT 
  * 
FROM 
  (
    FROM 
      * 
    FROM 
      events 
    WHERE 
      date = '2021-01-02'
  ) a 
  LEFT JOIN (
    SELECT 
      * 
    FROM 
      users
  ) b ON a.date = b.birthday
SQL
  • GROUP BY、HAVING、ORDER BY 中的别名替换逻辑与标准 SQL 行为更一致，即别名仅在顶级表达式有效，而在子表达式中无效。例如：
/* 支持的语法 */
SELECT NOT bool_col AS nb
FROM t
GROUP BY nb
HAVING nb;
 
/* 不再支持的语法 */
SELECT int_col / 2 AS x
FROM t
GROUP BY x
HAVING x > 3;
SQL
  • 新增了一系列的保留字段，这些字段是不能直接用作标识符的。如果需要将其用作标识符，则必须用反引号引起来，例如：
/* 支持的语法 */
SELECT `position` FROM events
 
/* 不再支持的语法 */
SELECT position FROM events
SQL
  • 新增的保留字段包括：
allocate、any、api_version、are、array_agg、array_max_cardinality、asensitive、asymmetric、at、atomic、authorization、begin_frame、begin_partition、blob、block_size、both、called、cardinality、cascaded、character、clob、close_fn、collate、collect、commit、condition、connect、constraint、contains、convert、copy、corr、corresponding、covar_pop、covar_samp、cube、current_date、current_default_transform_group、current_path、current_role、current_row、current_schema、current_time、current_transform_group_for_type、cursor、cycle、deallocate、dec、decfloat、declare、define、deref、deterministic、disconnect、dynamic、each、element、empty、end-exec、end_frame、end_partition、equals、escape、every、except、exec、execute、fetch、filter、finalize_fn、foreign、frame_row、free、fusion、get、global、grouping、groups、hold、indicator、init_fn、initial、inout、insensitive、intersect、intersection、json_array、json_arrayagg、jso、n_exists、json_object、json_objectagg、json_query、json_table、json_table_primitive、json_value、large、lateral、leading、like_regex、listagg、local、localtimestamp、log10、match、match_number、match_recognize、matches、merge、merge_fn、method、modifies、multiset、national、natural、nchar、nclob、no、none、normalize、nth_value、nth_value、occurrences_regex、octet_length、of、off、omit、one、only、out、overlaps、overlay、pattern、per、percent、percentile_cont、percentile_disc、portion、position、position_regex、precedes、prepare、prepare_fn、procedure、ptf、reads、recursive、ref、references、regr_avgx、regr_avgy、regr_count、regr_intercept、regr_r2、regr_slope、regr_sxx、regr_sxy、regr_syy、release、rollback、rollup、running、savepoint、scope、scroll、search、seek、serialize_fn、similar、skip、some、specific、specifictype、sqlexception、sqlexception、sqlwarning、static、straight_join、submultiset、subset、substring_regex、succeeds、symmetric、system_time、system_user、timezone_hour、timezone_minute、trailing、translate_regex、translation、treat、trigger、trim_array、uescape、unique、unnest、update_fn、value_of、varbinary、varying、versioning、whenever、width_bucket、window、within、without

Impala 保留字段参考文档