1、新增字段
ALTER TABLE dws.表名字 ADD COLUMN 字段名字 Nullable(Float32) DEFAULT NULL
2逢唤、修改字段名稱(chēng)
ALTER TABLE dws.表名字 RENAME COLUMN 舊名字 TO 新名字
優(yōu)秀代碼記錄：
(1) 用戶留存代碼
with
14 as len, -101 as fill
select
arrayResize(arrayMap(x->x[2], arraySort(x->x, groupArray([period, impression/retention]))), len, fill) as days
from
(
select
date, toInt16(period) as period, sum(impression) as impression,sum(retention) as retention,
sum(case when period = '0' then cost else 0 end ) as cost, sum(case when period = '0' then install_num else 0 end ) as install_num
from
dws.dws_overseas_new_users_data
where
date >= today()-8
and country = 'US'
group by
date, period )
group by
date
(2) 漏斗分析代碼
SELECT media_source AS media_source, funnel_sort AS funnel_sort, count(1) AS total_count
FROM (SELECT media_source,
arraySort(x ->
-x, groupArray(count)) AS funnel_sort
FROM (SELECT media_source, level_index, count(1) AS count
FROM (SELECT media_source,
user_id AS user_id,
arrayJoin(arrayEnumerate(levels)) AS level_index,
arrayWithConstant(level, 1) AS levels
FROM (SELECT media_source,
user_id,
windowFunnel(86400)(parseDateTimeBestEffort(event_time), event_name = 'bf_enter_success',
event_name = 'bf_ad_show', event_name = 'StayTimePerMinute',
event_name = 'RewardVideoShowSuccess') AS level
FROM (SELECT date AS date,
country AS country,
app_name AS app_name,
app_version AS app_version,
media_source AS media_source,
user_id AS user_id,
event_name AS event_name,
app_id AS app_id,
event_time AS event_time
FROM ods.ods_appsflyer_event
WHERE 1 = 1
AND date BETWEEN '2020-10-01' AND '2020-10-18'
AND media_source in ('google', 'facebook', 'applovin', 'Apple Search Ads')
AND app_name like ('%%Build%%')
AND app_id = ('APAGQ4DANBZMYZTK')
LIMIT 100000 OFFSET 0)
WHERE 1 = 1
GROUP BY media_source, user_id)
WHERE 1 = 1)
WHERE 1 = 1
GROUP BY media_source, level_index)
WHERE 1 = 1
GROUP BY media_source
LIMIT 1000 OFFSET 0)
WHERE 1 = 1
GROUP BY media_source, funnel_sort
SELECT media_source AS media_source, funnel_sort AS funnel_sort, count(1) AS total_count
FROM (SELECT media_source,
arraySort(x ->-x, groupArray(count)) AS funnel_sort
FROM (SELECT media_source, level_index, count(1) AS count
FROM (SELECT media_source,
user_id AS user_id,
arrayJoin(arrayEnumerate(levels)) AS level_index,
arrayWithConstant(level, 1) AS levels
FROM (SELECT media_source,
user_id,
windowFunnel(1468800)(parseDateTimeBestEffort(event_time),
event_name = 'bf_enter_success', event_name = 'bf_ad_show',
event_name = 'StayTimePerMinute',
event_name = 'RewardVideoShowSuccess') AS level
FROM (SELECT date AS date,
country AS country,
app_name AS app_name,
app_version AS app_version,
media_source AS media_source,
user_id AS user_id,
event_name AS event_name,
app_id AS app_id,
event_time AS event_time
FROM ods.ods_appsflyer_event
WHERE 1 = 1
AND date BETWEEN '2020-10-01' AND '2020-10-18'
AND app_id = ('APAGQ4DANBZMYZTK'))
WHERE 1 = 1
GROUP BY media_source, user_id)
WHERE 1 = 1)
WHERE 1 = 1
GROUP BY media_source, level_index)
WHERE 1 = 1
GROUP BY media_source)
WHERE 1 = 1
GROUP BY media_source, funnel_sort
--unique_user+oneday--
SELECT media_source AS media_source, funnel_sort AS funnel_sort, count(1) AS total_count
FROM (SELECT media_source,
arraySort(x ->
-x, groupArray(count)) AS funnel_sort
FROM (SELECT media_source, level_index, count(1) AS count
FROM (SELECT media_source,
user_id AS user_id,
arrayJoin(arrayEnumerate(levels)) AS level_index,
arrayWithConstant(level, 1) AS levels
FROM (SELECT media_source,
user_id,
windowFunnel(86400)(parseDateTimeBestEffort(event_time), event_name = 'bf_enter_success',
event_name = 'bf_ad_show', event_name = 'StayTimePerMinute',
event_name = 'RewardVideoShowSuccess') AS level
FROM (SELECT date AS date,
country AS country,
app_name AS app_name,
app_version AS app_version,
media_source AS media_source,
user_id AS user_id,
event_name AS event_name,
app_id AS app_id,
event_time AS event_time
FROM ods.ods_appsflyer_event
WHERE 1 = 1
AND date BETWEEN '2020-10-01' AND '2020-10-18'
AND app_id = ('APAGQ4DANBZMYZTK'))
WHERE 1 = 1
GROUP BY media_source, user_id)
WHERE 1 = 1)
WHERE 1 = 1
GROUP BY media_source, level_index)
WHERE 1 = 1
GROUP BY media_source)
WHERE 1 = 1
GROUP BY media_source, funnel_sort
