??????
????kylin ??????????????????????
????????sql??
SELECT
c.DT,
COALESCE (C.weekActivityCount, 0),
COALESCE (D.monthActivityCount, 0)
FROM
(
SELECT
DT AS DT,
count(*) AS weekActivityCount
FROM
ST_DEVICE_INFO_FACT_TABLE
WHERE
6 >= LAST_ONLINE_DT_DIFF_DAY
AND LAST_ONLINE_DT_DIFF_DAY >= 0
AND DT >= '2018-10-11'
AND '2018-10-11' >= DT
AND IS_ACTIVATED = 1
GROUP BY
dt
) C
LEFT JOIN (
SELECT
DT AS DT,
count(*) AS monthActivityCount
FROM
ST_DEVICE_INFO_FACT_TABLE
WHERE
29 >= LAST_ONLINE_DT_DIFF_DAY
AND LAST_ONLINE_DT_DIFF_DAY >= 0
AND DT >= '2018-10-11'
AND '2018-10-11' >= DT
AND IS_ACTIVATED = 1
GROUP BY
dt
) D ON C.DT = D.DT
????????cube??
CUBE[name=st_device_info_cube_v140],CUBE[name=st_device_info_cube_v140]
????????????
2018-10-11??996542??996542
????????????C,D????????????
SELECT
DT AS DT,
count(*) AS weekActivityCount
FROM
ST_DEVICE_INFO_FACT_TABLE
WHERE
6 >= LAST_ONLINE_DT_DIFF_DAY
AND LAST_ONLINE_DT_DIFF_DAY >= 0
AND DT >= '2018-10-11'
AND '2018-10-11' >= DT
AND IS_ACTIVATED = 1
GROUP BY
dt
Cubes: CUBE[name=st_device_info_cube_v140]
????????????
2018-10-11??996542
??????
SELECT
DT AS DT,
count(*) AS monthActivityCount
FROM
ST_DEVICE_INFO_FACT_TABLE
WHERE
29 >= LAST_ONLINE_DT_DIFF_DAY
AND LAST_ONLINE_DT_DIFF_DAY >= 0
AND DT >= '2018-10-11'
AND '2018-10-11' >= DT
AND IS_ACTIVATED = 1
GROUP BY
dt
Cubes: CUBE[name=st_device_info_cube_v140]
????????????
2018-10-11??1119847
????????????????left join ????????????????????????????????
????????????????????????
