davinci报表使用的sql参考
第一张表：
SELECT T.day as "日期",
COUNT((
CASE
WHEN (fun_code='1011010003') THEN
fun_code
ELSE
NULL
end
)) as "总安装量",
COUNT((
CASE
WHEN (fun_code='1011010001') THEN
fun_code
ELSE
NULL
end
)) as "总注册量",
COUNT((
CASE
WHEN (is_month='1') THEN
is_month
ELSE
NULL
end
)) as "月活账号数量",
COUNT((
CASE
WHEN (is_week='1') THEN
is_week
ELSE
NULL
end
)) as "周活账号数量"
FROM
(SELECT m.*, s.event_property_code,s.event_property_value FROM cms.daily_new_clientrpt_master m LEFT JOIN cms.daily_new_clientrpt_slave s on m.event_id= s.event_id ) T where DATE_SUB(NOW( ), INTERVAL 30 DAY) <= server_time GROUP BY "日期"
第二张表：
SELECT
T.dept_one as "用户对象",
AVG((
CASE
WHEN (server_time =toDate($g$)) THEN
member_count
ELSE
NULL
end
)) as "总人数",
COUNT((
CASE
WHEN (fun_code='1011010001') THEN
fun_code
ELSE
NULL
end
)) as "总注册量",
COUNT((
CASE
WHEN (fun_code='1011010001' and server_time =toDate($g$)) THEN
fun_code
ELSE
NULL
end
)) as "当日注册量",
COUNT((
CASE
WHEN (is_month='1') THEN
is_month
ELSE
NULL
end
)) as "月活账号数量",
COUNT((
CASE
WHEN (is_week='1') THEN
is_week
ELSE
NULL
end
)) as "周活账号数量"
FROM
(SELECT m.*, s.event_property_code,s.event_property_value FROM cms.daily_new_clientrpt_master m LEFT JOIN cms.daily_new_clientrpt_slave s on m.event_id= s.event_id ) T GROUP BY "用户对象" ;
第三张表：
SELECT
T.fun_name as "模块名",
SUM(click_records) as "点击数量",
SUM(stay_time) as "使用时长"
FROM
(SELECT m.*, s.event_property_code,s.event_property_value FROM cms.daily_new_clientrpt_master m LEFT JOIN cms.daily_new_clientrpt_slave s on m.event_id= s.event_id ) T where server_time = toDate($g$) GROUP BY "模块名";
SELECT
T.fun_name as "模块名",
SUM(click_records) as "点击数量",
SUM(stay_time)/1000 as "使用时长(单位：秒)"
FROM
(SELECT m.*, s.event_property_code,s.event_property_value FROM cms.daily_new_clientrpt_master m LEFT JOIN cms.daily_new_clientrpt_slave s on m.event_id= s.event_id ) T where day = today() GROUP BY "模块名";
上一篇： 安装oracle9i 9.2 for ia64 手记
下一篇： 【转】PHP变量使用大括号的异同