137 lines
3.7 KiB
SQL
137 lines
3.7 KiB
SQL
INSERT INTO ads_new_device_ltv_statis
|
|
SELECT appid, channel, ds, new_add, SUM(CASE
|
|
WHEN rd >= 0 AND pd <= 0 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv1, SUM(CASE
|
|
WHEN rd >= 1 AND pd <= 1 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv2, SUM(CASE
|
|
WHEN rd >= 2 AND pd <= 2 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv3, SUM(CASE
|
|
WHEN rd >= 3 AND pd <= 3 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv4, SUM(CASE
|
|
WHEN rd >= 4 AND pd <= 4 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv5, SUM(CASE
|
|
WHEN rd >= 5 AND pd <= 5 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv6, SUM(CASE
|
|
WHEN rd >= 6 AND pd <= 6 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv7, SUM(CASE
|
|
WHEN rd >= 7 AND pd <= 7 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv8, SUM(CASE
|
|
WHEN rd >= 8 AND pd <= 8 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv9, SUM(CASE
|
|
WHEN rd >= 9 AND pd <= 9 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv10, SUM(CASE
|
|
WHEN rd >= 10 AND pd <= 10 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv11, SUM(CASE
|
|
WHEN rd >= 11 AND pd <= 11 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv12, SUM(CASE
|
|
WHEN rd >= 12 AND pd <= 12 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv13, SUM(CASE
|
|
WHEN rd >= 13 AND pd <= 13 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv14, SUM(CASE
|
|
WHEN rd >= 14 AND pd <= 14 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv15, SUM(CASE
|
|
WHEN rd >= 15 AND pd <= 15 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv16, SUM(CASE
|
|
WHEN rd >= 16 AND pd <= 16 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv17, SUM(CASE
|
|
WHEN rd >= 17 AND pd <= 17 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv18, SUM(CASE
|
|
WHEN rd >= 18 AND pd <= 18 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv19, SUM(CASE
|
|
WHEN rd >= 19 AND pd <= 19 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv20, SUM(CASE
|
|
WHEN rd >= 20 AND pd <= 20 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv21, SUM(CASE
|
|
WHEN rd >= 21 AND pd <= 21 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv22, SUM(CASE
|
|
WHEN rd >= 22 AND pd <= 22 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv23, SUM(CASE
|
|
WHEN rd >= 23 AND pd <= 23 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv24, SUM(CASE
|
|
WHEN rd >= 24 AND pd <= 24 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv25, SUM(CASE
|
|
WHEN rd >= 25 AND pd <= 25 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv26, SUM(CASE
|
|
WHEN rd >= 26 AND pd <= 26 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv27, SUM(CASE
|
|
WHEN rd >= 27 AND pd <= 27 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv28, SUM(CASE
|
|
WHEN rd >= 28 AND pd <= 28 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv29, SUM(CASE
|
|
WHEN rd >= 29 AND pd <= 29 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv30, SUM(CASE
|
|
WHEN rd >= 30 AND pd <= 30 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv31, SUM(CASE
|
|
WHEN rd >= 45 AND pd <= 45 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv45, SUM(CASE
|
|
WHEN rd >= 60 AND pd <= 60 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv60, SUM(CASE
|
|
WHEN rd >= 90 AND pd <= 90 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv90, SUM(CASE
|
|
WHEN rd >= 120 AND pd <= 120 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv120, SUM(CASE
|
|
WHEN rd >= 150 AND pd <= 150 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv150, SUM(CASE
|
|
WHEN rd >= 180 AND pd <= 180 THEN payment_amount
|
|
ELSE 0
|
|
END) AS ltv180
|
|
FROM (
|
|
SELECT a.appid, a.channel, a.ds, a.newly_device AS new_add, TIMESTAMPDIFF(day, b.ds, a.ds) AS rd, b.rd AS pd,
|
|
b.payment_amount
|
|
FROM (
|
|
SELECT appid, (CASE
|
|
WHEN GROUPING(channel) = 0 THEN channel
|
|
ELSE '__default__'
|
|
END) AS channel, ds, COUNT(user_id) AS newly_device
|
|
FROM ods_newly_device
|
|
WHERE ds >= '${lastdate_180}' AND ds <= '${bizdate}'
|
|
GROUP BY GROUPING SETS((appid, ds), (appid, channel, ds))
|
|
) AS a
|
|
LEFT JOIN (
|
|
SELECT appid, channel, rd, ds, SUM(amount) AS payment_amount
|
|
FROM dwd_ltv_new_device
|
|
WHERE ds >= '${lastdate_180}' AND rd >= 0 AND rd <= 180
|
|
GROUP BY GROUPING SETS((appid, ds, rd), (appid, channel, ds, rd))
|
|
) AS b
|
|
ON a.appid = b.appid AND a.channel = b.channel AND a.ds = b.ds
|
|
) AS c
|
|
GROUP BY appid,
|
|
channel,
|
|
ds,
|
|
new_add; |