select u_i.uid as uid, count(distinct act_month) as act_month_total, count(distinct case when year(act_time) = 2021 then act_day end) as act_days_2021, count(distinct case when year(act_time) = 2021 and tag = 'exam' then act_day end) as act_days_2021_exam, count(distinct case when year(act_time) = 2021 and tag = 'question' then act_day end) as act_days_2021_question from user_info u_i left join (select uid, start_time as act_time, date_format(start_time, '%Y%m') as act_month, date_format(start_time, '%Y%m%d') as act_day, 'exam' as tag from exam_record union all select uid, submit_time as act_time, date_format(submit_time, '%Y%m') as act_month, date_format(submit_time, '%Y%m%d') as act_day, 'question' as tag from practice_record ) exam_and_practice on exam_and_practice.uid = u_i.uid where u_i.level >= 6 group by uid order by act_month_total desc, act_days_2021 desc
时间: 2024-02-14 15:24:00 浏览: 46
这是一条 SQL 查询语句,用于查询用户的活跃度。具体来说,它会统计用户在不同时间段内参加考试或练习的次数,并按照总活跃度和2021年活跃度排序。其中,act_month_total 表示用户参加考试或练习的活跃月份数,act_days_2021 表示用户在2021年参加考试或练习的天数,act_days_2021_exam 和 act_days_2021_question 分别表示用户在2021年参加考试或练习、且标记为“exam”或“question”的天数。该语句使用了左连接和子查询等高级语法,适用于较大规模的数据查询。
相关问题
select u_i.uid as uid, count(distinct act_month) as act_month_total, count(distinct case when year(act_time) = 2021 then act_day end) as act_days_2021, count(distinct case when year(act_time) = 2021 and tag = 'exam' then act_day end) as act_days_2021_exam, count(distinct case when year(act_time) = 2021 and tag = 'question' then act_day end) as act_days_2021_question from user_info u_i left join (select uid, start_time as act_time, date_format(start_time, '%Y%m') as act_month, date_format(start_time, '%Y%m%d') as act_day, 'exam' as tag from exam_record union all select uid, submit_time as act_time, date_format(submit_time, '%Y%m') as act_month, date_format(submit_time, '%Y%m%d') as act_day, 'question' as tag from practice_record ) exam_and_practice on exam_and_practice.uid = u_i.uid where u_i.level >= 6 group by uid order by act_month_total desc, act_days_2021 desc; 请用oracle语法来改写这段sql
以下是使用 Oracle 语法改写后的 SQL 查询语句:
```
SELECT u_i.uid,
COUNT(DISTINCT act_month) AS act_month_total,
COUNT(DISTINCT CASE
WHEN EXTRACT(YEAR FROM act_time) = 2021 THEN act_day
END) AS act_days_2021,
COUNT(DISTINCT CASE
WHEN EXTRACT(YEAR FROM act_time) = 2021 AND tag = 'exam' THEN act_day
END) AS act_days_2021_exam,
COUNT(DISTINCT CASE
WHEN EXTRACT(YEAR FROM act_time) = 2021 AND tag = 'question' THEN act_day
END) AS act_days_2021_question
FROM user_info u_i
LEFT JOIN (SELECT uid,
start_time AS act_time,
TO_CHAR(start_time, 'YYYYMM') AS act_month,
TO_CHAR(start_time, 'YYYYMMDD') AS act_day,
'exam' AS tag
FROM exam_record
UNION ALL
SELECT uid,
submit_time AS act_time,
TO_CHAR(submit_time, 'YYYYMM') AS act_month,
TO_CHAR(submit_time, 'YYYYMMDD') AS act_day,
'question' AS tag
FROM practice_record) exam_and_practice
ON exam_and_practice.uid = u_i.uid
WHERE u_i.level >= 6
GROUP BY u_i.uid
ORDER BY act_month_total DESC, act_days_2021 DESC;
```
在 Oracle 中,日期时间的函数使用 `EXTRACT`,日期格式化使用 `TO_CHAR`。同时,Oracle 中使用分号作为语句结束符。
select t1.month_id, count(distinct case when t1.offer_type_name='主套餐' then offer_name end) aa, count(distinct case when t1.offer_type_name='促销' then offer_name end) bb, count(distinct case when t1.offer_type_name='可选包' then offer_name end) cc, count(distinct case when t2.offer_type_name='主套餐' then offer_name end) aaa, count(distinct case when t2.offer_type_name='促销' then offer_name end) bbb, count(distinct case when t2.offer_type_name='可选包' then offer_name end) ccc, from zone_crmchk.dwd_sum_zhuliuxxsc t1 left join zone_crmchk.dwd_sum_zhuliuxxsc t2 on t2.month < t1.month group by t1.month, count(distinct case when offer_type_name='主套餐' then offer_name end) aa, count(distinct case when offer_type_name='促销' then offer_name end) bb, count(distinct case when offer_type_name='可选包' then offer_name end) cc
根据您提供的 SQL 查询语句,我发现其中存在一个逗号(,)出现在了 FROM 子句之后,这是一个语法错误。您需要将逗号(,)删除,同时将查询语句修改为如下形式:
```
SELECT
t1.month_id,
COUNT(DISTINCT CASE WHEN t1.offer_type_name='主套餐' THEN offer_name END) aa,
COUNT(DISTINCT CASE WHEN t1.offer_type_name='促销' THEN offer_name END) bb,
COUNT(DISTINCT CASE WHEN t1.offer_type_name='可选包' THEN offer_name END) cc,
COUNT(DISTINCT CASE WHEN t2.offer_type_name='主套餐' THEN offer_name END) aaa,
COUNT(DISTINCT CASE WHEN t2.offer_type_name='促销' THEN offer_name END) bbb,
COUNT(DISTINCT CASE WHEN t2.offer_type_name='可选包' THEN offer_name END) ccc
FROM
zone_crmchk.dwd_sum_zhuliuxxsc t1
LEFT JOIN zone_crmchk.dwd_sum_zhuliuxxsc t2 ON t2.month < t1.month
GROUP BY
t1.month_id,
COUNT(DISTINCT CASE WHEN offer_type_name='主套餐' THEN offer_name END),
COUNT(DISTINCT CASE WHEN offer_type_name='促销' THEN offer_name END),
COUNT(DISTINCT CASE WHEN offer_type_name='可选包' THEN offer_name END);
```
这样修改后,查询语句应该就能正确执行了。
阅读全文