select date_format(submit_time,'%Y%m') submit_month, count( distinct uid,submit_time) month_q_cnt , ase when month(submit_time) in ('1','3','5','7','8','10','12') then round(count(distinct uid,submit_time)/31,2) , case when month(submit_time) in ('4','6','9','11') then round(count(distinct uid,submit_time)/30,2) , else round(count(distinct uid,submit_time)/28,2) end avg_day_q_cnt from practice_record where year(submit_time)='2021' group by submit_month 这段sql有什么错误
时间: 2023-08-30 09:07:02 浏览: 54
这段 SQL 语句中有一个语法错误,即 `ase` 应该是 `case`。修正后的 SQL 语句如下:
```
select date_format(submit_time,'%Y%m') submit_month,
count(distinct uid,submit_time) month_q_cnt,
case when month(submit_time) in ('1','3','5','7','8','10','12') then round(count(distinct uid,submit_time)/31,2)
when month(submit_time) in ('4','6','9','11') then round(count(distinct uid,submit_time)/30,2)
else round(count(distinct uid,submit_time)/28,2) end avg_day_q_cnt
from practice_record
where year(submit_time)='2021'
group by submit_month;
```
修正后的 SQL 语句中,将 `ase` 改为了 `case`,并且在 `case when` 子句中,将第二个 `case when` 改为了 `when`,同时增加了 `end` 关键字,使得语法正确。
相关问题
round((count(distinct uid, date_format(submit_time, '%y%m%d'))) / count(distinct uid), 2) as avg_active_days 怎么理解
这段 SQL 语句的作用是计算平均活跃天数,具体解释如下:
1. `count(distinct uid, date_format(submit_time, '%y%m%d'))`:这部分代码是对(uid, date)进行去重计数,即计算出有多少个不同的用户在不同的日期提交了数据。
2. `count(distinct uid)`:这部分代码是计算所有不同的用户数,即计算出有多少个不同的用户在整个时间段内提交了数据。
3. `avg_active_days`:这部分代码是计算平均活跃天数的结果,并将结果保留两位小数。
综合上述三部分,可以理解为:将总用户数除以不同用户在不同日期提交数据的数量,得到平均活跃天数。这个指标可以用来衡量用户的留存情况和活跃度。
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 中使用分号作为语句结束符。
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)