分析 > 经营分析 > 自定义查询 。
自定义查询的数据表增加了表级权限控制,如何开启数据表权限参考 数据表管理 - 权限 。
在编辑框中输入要查询的 SQL,支持SQL关键字联想和格式化。
注: 自定义查询中使用 select * 语句能查询到隐藏的 预置属性 ,不能查询到隐藏的 自定义属性 。
支持查看SQL查询进度。
1、支持通过使用查询结果中的可视化图表工具,进行数据分析。
2、查询结果支持下载Excel和Csv文件。出于性能的考虑,前端展示的结果最大只有 1k 条,而 CSV 下载的结果最大是 100w 条,如果需要下载更多数据请使用 查询 API 。
1、支持对当前查询的SQL进行保存,SQL名称支持自定义,并支持添加备注信息。
2、可双击保存的查询列表中的SQL语句,进行SQL语句查看和运行。
支持查看历史SQL查询记录,并支持对历史记录进行搜索。
支持对当前SQL查询结果,快速保存到报表。
在一些业务场景中,实际的数据结果都需要经过复杂计算才能在结果表上展现出来;除此之外还有一些数据量较大的表,在其上进行统计查询通常会效率很低;为了解决查询逻辑复杂、数据量大导致的性能问题,神策上线了中间表的功能。
一、开通中间表功能后,点击【保存中间表】
二、打开【创建SQL任务】页面,支持通过中间表调度周期。配置方法详见: 任务管理
三、点击保存后,同时保存一条任务到【任务管理】中;一条数据表到【数据表管理】列表中
四、用户可以在【数据表管理】中对中间表做数据权限分发
五、创建成功后的数据表,可在自定义查询中应用
如何使用session表创建指标:需要在查询语句中加入/*SESSION_TABLE_DATE_RANGE=[2018-01-01,2018-01-05]*/
/*event表和session表创建中间表示例*/
SELECT sessions_session.event,
sessions_session.user_id,
sessions_session.distinct_id,
sessions_session.`date`,
EVENTS.showEntrance,
EVENTS.action_type
FROM EVENTS
JOIN sessions_session ON sessions_session.user_id = events.user_id/*SESSION_TABLE_DATE_RANGE=[2018-01-01,2018-01-05]*/
where events.`date` between '2022-03-21' and '2022-03-27'
如在 游戏行业 中,客户将找茬游戏的数据存在项目一中,拼图游戏的数据存在项目二中,策略抽卡游戏的数据存在项目三中,其中找茬和拼图游戏主要是为策略游戏导流。那么 跨项目查询 可以满足以下场景的需求:
场景一大盘数据查看 :管理层在一个报表中查看三种类型游戏的日活、留存、充值等汇总数据,实现步骤如下:
场景二数据洞察 :对项目一 / 项目二中转化为项目三用户情况进行分析等,实现步骤如下:
一、如何获得自己有权限的项目英文名以及项目 ID :点击页面右上角「查看项目列表」弹出项目列表弹窗。支持查看用户自己有权限的项目名称、项目ID、项目英文名、以及操作,点击操作列的查看项目可打开对应项目页面
二、如何了解各个项目内的表结构 :目前仅支持切换到具体项目之后进入自定义查询页面查看
三、什么情况下需要指定数据表所属项目 : 书写一个可进行跨项目查询数据的 SQL 语句,就需要指定所查询的神策的数据表的所属项目
rawdata下的events users items表、分群、标签、session表可用固定前缀「horizon」+ 「_${projectName}_${projectId}」来修饰数据表进行置顶所属项目,以项目名为 「production」项目 ID 为 「2」的项目举例(这里的项目前缀修饰起到了一个限制 db 的作用):
其他数据表、报表、中间表他们都有自己的db,直接db.表名
SELECT 'production' as project, date, count(1)
FROM horizon_production_2.events
WHERE event like 'AppInstall' and `date` > '2022-12-01'
GROUP BY 1,2
UNION ALL
SELECT 'default' as project, date, count(1)
FROM horizon_default_1.events
WHERE event = 'AppInstall' and `date` > '2022-12-01'
GROUP BY 1,2
SELECT count(1)
FROM horizon_production_2.events events
INNER JOIN horizon_default_1.user_group_user_group_1 user_group1 ON events.user_id = user_group1.user_id
WHERE event = 'AppClick' and `date` = CURRENT_DATE()
SELECT e1.event,
e2.date,
e2.distinct_id,
e2.`$element_target_url`,
e2.`$country`,
e2.`$utm_campaign`
FROM horizon_production_2.events e1
LEFT JOIN horizon_zwp_001_3.events e2 ON e1.event = e2.event
根据登录用户在 SBP数据权限、脱敏属性配置页面,对需要进行事件属性或者用户属性需要脱敏字段的配置,在hue界面书写的sql语句中,对脱敏字段进行预校验。
【现阶段对于脱敏显示、禁止分组与筛选 不做区分,SQL中涉及到了脱敏属性直接报错】
目前实现的 SQL 解析效果:
支持元事件辅助信息查看。默认展示全部事件,点击“事件名”或操作列“查看”,可查看该事件对应的属性列表。支持复制事件名、属性名的复制操作,复制后需手动粘贴(ctrl+v 或 右键 → 粘贴)插入SQL编辑区
date 字段表示事件发生时的日期,精确到天,可以用于快速过滤数据。需要特别注意,任何时候都应当尽量使用 date 字段进行过滤,而不是 time 字段。
由于 date 字段的特殊性,对 SQL 操作和函数的支持有一些限制,目前支持使用的函数和表达式有:
CURRENT_DATE() - INTERVAL '1' DAY
表示昨天。
以下是一些具体的例子:
SELECT COUNT(*) FROM events WHERE date = '2016-01-01'
SELECT COUNT(*) FROM events WHERE date = CURRENT_DATE()
SELECT COUNT(*) FROM events WHERE date BETWEEN CURRENT_DATE() - INTERVAL '2' DAY AND CURRENT_DATE()
SELECT COUNT(*) FROM events WHERE date BETWEEN CURRENT_MONTH() - INTERVAL '1' MONTH AND CURRENT_MONTH() - INTERVAL '1' DAY
由于 date 是专门为快速的数据过滤设计的特殊字段,不支持绝大多数的时间函数。因此,如果希望使用其它时间函数,请使用 time 字段代替,例如:
SELECT datediff(now(), trunc(time, 'DD')), COUNT(*) FROM events WHERE date >= CURRENT_DATE() - INTERVAL '100' day GROUP BY 1
SELECT date_sub(date,dayofmonth(date)-1) the_month,count(*) event_qty
FROM events WHERE date>'2018-09-01'
GROUP BY the_month ORDER BY the_month;
SELECT date_sub(date,mod(dayofweek(date)+5,7)) the_week,count(*) event_qty
FROM events WHERE date>'2018-09-01'
GROUP BY the_week ORDER BY the_week;
SELECT COUNT(DISTINCT user_id) FROM events
WHERE date = CURRENT_DATE() /*ENABLE_APPROX_DISTINCT*/
SELECT $model FROM events
WHERE date = CURRENT_DATE() /*ENABLE_DIMENSION_DICT_MAPPING*/
SELECT event, time FROM events
WHERE date = CURRENT_DATE() AND distinct_id='abcdef' /*DISTINCT_ID_FILTER=abcdef*/
SELECT * FROM events WHERE date = CURRENT_DATE() LIMIT 1000 /*MAX_QUERY_EXECUTION_TIME=1800*/
SELECT COUNT(*) AS cnt FROM events
JOIN /* +SHUFFLE */ users ON events.user_id = users.id
WHERE date = CURRENT_DATE()
直接使用 distinct_id 查询即可:
SELECT * FROM events WHERE distinct_id = 'wahaha' AND date = '2015-09-10' LIMIT 100
使用标准的 SQL 日期函数 EXTRACT 来取出小时信息。
SELECT date, COUNT(*) FROM events
WHERE EXTRACT(HOUR FROM time) IN (10, 11) AND event = 'SubmitOrder'
GROUP BY 1
首先计算每个用户的下单次数,然后使用 CASE..WHEN 语法来分组。
SELECT
WHEN c < 10 THEN '<10'
WHEN c < 20 THEN '<20'
WHEN c < 100 THEN '<100'
ELSE '>100'
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
GROUP BY 1
使用 LEFT OUTER JOIN 计算差集。
SELECT a.user_id FROM (
SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'BuyGold'
LEFT OUTER JOIN (
SELECT DISTINCT user_id FROM events WHERE date='2015-10-1' AND event = 'SaleGold'
ON a.user_id = b.user_id
WHERE b.user_id IS NULL
使用分析函数,根据每个用户相邻的两个事件的间隔估算累计使用时长,如果两次使用间隔超出 10 分钟则不计算。
SELECT
user_id,
CASE WHEN
end_time - begin_time < 600
end_time - begin_time
) 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'
GROUP BY 1
使用 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
如果属性有维度字典话需要客户可使用增加了$dimension_dict_属性名或事件名
/* 查询上传过维度字典的 orgName 属性示例 */
SELECT DISTINCT $dimension_dict_orgName
FROM users
/* 查询上传过维度字典的 $sf_strategy_unit_id 属性示例 */
SELECT DISTINCT $dimension_dict_$sf_strategy_unit_id
FROM events
自定义查询引擎切换为直接查引擎模式,并将底层查询引擎版本升级到了 4.8,因此存在少量的不兼容语法变更。
/* 支持的语法 */
SELECT * FROM events WHERE date = '2021-03-01' + interval 1 day
/* 不再支持的语法 */
SELECT * FROM events WHERE date - 1 = '2021-03-01'
/* 有性能损失的用法 */
SELECT
events
WHERE
date = '2021-01-02'
LEFT JOIN (
SELECT
users
) b ON a.date = b.birthday
/* 支持的语法 */
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;
/* 支持的语法 */
SELECT `position` FROM events
/* 不再支持的语法 */
SELECT position FROM events
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 |