我一开始想着用exist，可能是没掌握具体语法，导致没输出出来，后面懂逻辑了；想问一下，不关注代码量，只关注性能的话，exist是不是比join的消耗资源要少很多啊
SELECT
t1.*
FROM
didi_sht_rcd t1
WHERE
EXISTS (
SELECT 1
FROM loc_nm_ctg t2
WHERE t2.loc_nm = t1.start_loc AND t2.loc_ctg = '餐饮'
)
AND
EXISTS (
SELECT 1
FROM loc_nm_ctg t3
WHERE t3.loc_nm = t1.end_loc AND t3.loc_ctg = '餐饮'
)
我这个表格跟输出示例是一样的呀，答案里面没有保留2位小数
with total_typ_cnt as
(select
v_typ
,count(distinct usr_id) as total_user
from
bilibili_t20 as bt2
join
bilibili_t3 as bt3
on
bt2.v_id=bt3.v_id
group by
1),
usr_see_typ as (
select
usr_id
,count(distinct v_typ) as typ_cnt
from
bilibili_t20 as bt2
join
bilibili_t3 as bt3
on
bt2.v_id=bt3.v_id
group by
1
)
select
t.v_typ,
count(distinct case when typ_cnt >= 3 then u.usr_id end) as multi_category_viewers
, t.total_user as total_viewers,
round(count(distinct case when typ_cnt >= 3 then u.usr_id end)/ t.total_user*100,2) as mcv_index
from
bilibili_t20 as bt2
join bilibili_t3 as bt3 on bt2.v_id = bt3.v_id
join usr_see_typ as u on bt2.usr_id = u.usr_id
join total_typ_cnt as t on bt3.v_typ = t.v_typ
group by
t.v_typ, t.total_user
order by
4 desc
老师我觉得按照时间来划分梯队比较符合业务逻辑，但是最后计算出来的数字和答案不一样，我也不知道为什么
with userpurchase as
(select
user_id
,begin_date
,end_date
,pay_amount
,datediff(end_date,begin_date)+1 as duration
,case when datediff(end_date,begin_date)+1 between 20 and 32 then '月卡'
when datediff(end_date,begin_date)+1 between 80 and 99 then '季卡'
when datediff(end_date,begin_date)+1 between 170 and 190 then '半年卡'
when datediff(end_date,begin_date)+1 >200 then '年卡'
end as typ
from
bilibili_m1),
time_2021 as (
select
m_date
,y_m
from
bilibili_m2
where
year(m_date)=2021
),
typ_income as
(select
user_id
,u.begin_date
,u.end_date
,pay_amount
,duration
,pay_amount/duration as daily_income
,typ
,t.m_date
,y_m
from
time_2021 as t
join
userpurchase as u
on
t.m_date between u.begin_date and u.end_date)
select
round(sum(case when typ='月卡' then daily_income end),2) as yue
,round(sum(case when typ='季卡' then daily_income end),2) as ji
,round(sum(case when typ='半年卡' then daily_income end),2) as bannian
,round(sum(case when typ='年卡' then daily_income end),2) as nian
,round(sum(daily_income),2) as total
from
typ_income
select
area
,count(*) as total_companies
,count(case when (name like '%中国%' or name like '中%') then ts_code else null end) as chinese_named_companies
,round(count(case when (name like '%中国%' or name like '中%') then ts_code else null end )/count(*),3) as proportion
from
stock_info
group by
1
order by
3 desc
limit
5
这个地方的计算逻辑，我有点不太明白，代码就是计数统计那边有区别，结果差的很大
select
area
,count(*) as total_companies
,count(case when (name like '%中国%' or name like '中%') then ts_code else null end) as chinese_named_companies
,round(count(case when (name like '%中国%' or name like '中%') then ts_code else null end )/count(*),3) as proportion
from
stock_info
group by
1
order by
4 desc ===按比例排序哦，你写成第3列了。
limit
5
select
t2.snd_usr_id as user_a
,t2.rcv_usr_id as user_b
from
tx_red_pkt_rcd as t1
left join
tx_red_pkt_rcd as t2
on
t2.snd_usr_id=t1.rcv_usr_id
where
t2.rcv_usr_id=t1.snd_usr_id
and
DATE(t1.snd_datetime) = '2021-02-13'
AND DATE(t2.snd_datetime) = '2021-02-13'
and
t1.snd_usr_id<t1.rcv_usr_id
group by
1,2
输出结果不对
这个代码应该没有错呀
select
count(case when date(snd_datetime)='2021-02-13' then snd_usr_id end ) as
total_sent
,count(case when date(rcv_datetime)='1900-01-01' then snd_usr_id end ) as received_count
,100-round(count(case when date(rcv_datetime)='1900-01-01' then snd_usr_id end )/nullif(count(case when date(snd_datetime)='2021-02-13' then snd_usr_id end ),0)*100,2) as receive_rate
from
tx_red_pkt_rcd
不知道为什么错
WITH user_login_days AS (
select
usr_id
,date(login_time) as login_date
from
user_login_log
where
login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)),
rnk_user as(
select
usr_id
,login_date
,row_number()over(partition by usr_id order by login_date) as rnk
from
user_login_days
),
rnk_sub as(
select
usr_id
,login_date
,rnk
,date_sub(login_date,interval rnk day) as date_rnk
from
rnk_user
),
all_usr as(
select
usr_id
,date_rnk
,count(*) as 3_day_active
from
rnk_sub
group by
1,2
having
3_day_active>=3)
select
usr_id
,date_rnk
,date_add(date_rnk,interval 3_day_active day) as end_date
,3_day_active
from
all_usr
select
f.mch_id
,count(distinct f.cust_uid)
,count(distinct p.cust_uid)
,round(count(distinct p.cust_uid)/count(distinct f.cust_uid)*100,2)
from
xhs_fav_rcd as f
left join
xhs_pchs_rcd as p
on p.cust_uid=f.cust_uid
and p.mch_id=f.mch_id
and datediff(f.fav_tm,p.pchs_tm)<0
group by
1
order by
4 desc
还是有点迷糊，这一块，我的第一列应该没问题，但是第二列的问题不知道怎么解决
select
count(distinct f.cust_uid) as
conversion_user_count
from
xhs_fav_rcd as f
left join
xhs_pchs_rcd as p
on f.cust_uid=p.cust_uid
and f.mch_id=p.mch_id
and datediff(f.fav_tm,p.pchs_tm)<0
这个地方如果在表连接里面使用时间判断，左表有数据，右表没有数据，但是最后这条记录还是会保存下来对吧
select
concat(year(purchase_time),lpad(week(purchase_time),2,'0')) as week_number
,case when product_type='iPhone' then product_type
else 'Not iPhone' end as category
,count(distinct user_id) as user_count
from
apple_pchs_rcd
group by
1,2
order by
1,2
跟答案只有输出星期那一列有区别，但为什么数量差别这么大
不懂我的代码为什么运行不出来答案那个结果
select
date(login_time)
,count(*) as cnt
from
user_login_log
where
date(login_time) between '2024-09-01' and '2024-09-30'
and (usr_id,login_time)in
(select
usr_id
,min(login_time)
from
user_login_log
group by
1)
group by
1
order by
1
我知道要输出各个时间段的人，而不是登录用户数量，但是我有个疑问，通勤是两个时间段，那么早上有通勤记录，跟晚上有通勤记录，应该算作两次吧（单纯从理解这块的入手的话）
with login_typ as (
SELECT
usr_id,
CASE
WHEN TIME(login_time) BETWEEN '07:30:00' AND '09:30:00' OR TIME(login_time) BETWEEN '18:30:00' AND '20:30:00'
THEN 'commute'
WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00'
THEN 'break'
WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' OR TIME(login_time) BETWEEN '00:00:00' AND '01:00:00'
THEN 'bedtime'
ELSE NULL
END AS login_typ
FROM
user_login_log
WHERE
EXTRACT(YEAR FROM login_time) = EXTRACT(YEAR FROM CURRENT_DATE)
AND EXTRACT(MONTH FROM login_time) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL 1 MONTH) )
select
sum(case when login_typ='commute' then 1 else 0 end ) as commute
,sum(case when login_typ='break' then 1 else 0 end ) as lunch_break
,sum(case when login_typ='bedtime' then 1 else 0 end ) as bedtime
from
login_typ
老师我想知道怎么才能不漏掉两类股票都没上市的年份。难道是我还需要自己生成一个新表表述2000-2024年吗
with fillter_stock as (
select
ts_code
,symbol
,name
,area
,case when
industry='软件服务' then industry
else '地产'
end as industry
,list_date
from
stock_info
where
(year(list_date) between '2000' and '2024')
and industry rlike '地产|软件服务'),
done_stock as(
select
year(list_date) as Y
,industry
,count(industry) as stock_cnt
from
fillter_stock
group by
year(list_date)
,industry
)
select
Y
,max(case when industry='地产' then stock_cnt else 0 end) as '地产'
,max(case when industry='软件服务' then stock_cnt else 0 end) as '软件服务'
from
done_stock
group by
1
order by
1
我其实不是特别理解有两个点：
1、我这个代码输出的结果里面，第一行是null。但是我临时表1是输出的不同的2023月份，第二章合并表用的inner join，第三个临时表只是新增一列，应该不会出现null值
2、我第一个表中distinct 月份，然后第三章表进行左连接，4月份还掉了？按理来说date_table应该是完整的月份呀，不会出现这么大的误差的。
with date_summary as (
select
distinct date_format(date_value,'%Y-%m') as trx_mon
from
date_table
where
year(date_value)=2023
),
monthly_sum_trx_amt as (
select
substr(trx_time,1,7) as trx_mon
,sum(trx_amt) as month_trx_amt
from
cmb_usr_trx_rcd as u
inner join
cmb_mch_typ as m
using (mch_nm)
where
usr_id=5201314520
and mch_typ='休闲娱乐'
and year(trx_time)=2023
group by
substr(trx_time,1,7)
),
total_sum_trx_amt as (
select
m1.trx_mon
,coalesce(month_trx_amt,0) as month_trx_amt
,sum(month_trx_amt)over(order by m1.trx_mon ) as trx_amt
from
date_summary as d1
left join
monthly_sum_trx_amt as m1
on d1.trx_mon=m1.trx_mon
)
select
trx_mon
,trx_amt
from
total_sum_trx_amt
order by
trx_mon
with math_rank as (
select
student_id
,case when
score>=110 then '[110, 120]'
when
score between 90 and 109 then '[90, 110)'
when
score between 60 and 89 then '[60, 90)'
else '[0, 60)'
end as score_range
from
scores
where
exam_date='2024-06-30'
and subject='数学')
select
score_range
,count(*) as num_students
from
math_rank
group by
score_range
这个地方我输出的结果也是一样的，但是跟他排序不同。我想知道在这种情况下需要怎么排序，才能排成答案这种。
select
name
,subject
,class_code
,qualification
from
teachers
where
fir_degr rlike '北京大学|清华大学'
and head_teacher is not null
order by
name asc
我用正则表达式进行匹配为什么不对
select
st.student_id
,st.name
,sc.score
,row_number()over(order by sc.score desc) as rnk
from
students as st
inner join
scores as sc
on st.student_id=sc.student_id
where
st.grade_code='S1'
and sc.subject='物理'
ORDER BY
rnk,st.student_id
limit
10
就我这个也能输出排名前十，但是跟答案不一样，我筛选的条件也是s1学物理的
上一题中代码里面用的count计数，然后在临时表里没有group by，后面调用的时候进行分组。但是我在这个题中我在第二个计算各个类型频次表里，用count代替sum计数，然后我对日期和这几个count分组，输出不了结果，我不太明白为什么。
with quarterly_transactions as (
select
*,
concat(year(trx_time), '-', 'Q', quarter(trx_time)) as trx_quarter, /* 提取交易日期的年份和季度 */
count(case when trx_amt = 288 then trx_amt else null end) over (order by concat(year(trx_time), '-', 'Q', quarter(trx_time))) as withhand, /* 计算金额为 288 的交易次数，并按季度累积 */
count(case when trx_amt = 888 then trx_amt else null end) over (order by concat(year(trx_time), '-', 'Q', quarter(trx_time))) as doi /* 计算金额为 888 的交易次数，并按季度累积 */
from
cmb_usr_trx_rcd
where
usr_id = 5201314520 /* 限制查询结果只包含特定用户的交易记录 */ and mch_nm='红玫瑰按摩保健休闲'
and year(trx_time) in (2023,2024)
)
select
trx_quarter,
withhand,
doi
from
quarterly_transactions
group by
trx_quarter,
withhand,
doi /* 按季度分组，并选择累积的交易次数 */
order by
trx_quarter; /* 按季度排序 */
sum case when 和count case when绝大多数情况下都是可以等价互换的，你再试试是不是其他条件漏写啦
select
substr(DATE_FORMAT(trx_time, '%Y-%m-%d'),1,7) as trx_mon
,max(last_day(trx_time)) as last_day
from
cmb_usr_trx_rcd
where
year(trx_time) in (2023,2024)
group by
substr(DATE_FORMAT(trx_time, '%Y-%m-%d'),1,7)
order by
trx_mon
我想问一个语法方面的问题，就是有了group分组之后，是不是输出的列也是要聚合好的。我一开始lastday那里没有加max，就是无法输出。
select a,b from t group by a会报错的；select a, max(b) from t group by a不会报错。你的语句不加max，就是犯了第一种错
select
trx_amt
,count(trx_amt) as total_trx_cnt
,count(distinct usr_id) as unique_usr_cnt
,count(trx_amt)/count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲'
and year(trx_time)=2023 and month(trx_time) in (1,2,3,4,5,6,7,8,9,10,11,12)
or year(trx_time)=2024 and month(trx_time) in (1,2,3,4,5,6)
group by trx_amt
order by avg_trx_per_user desc
limit 5
我输出的结果中total_trx_cnt 数量不对，288.888,1288都多了两个
WITH TimeExtracted AS (
SELECT
cust_uid,
start_loc,
end_loc,
CAST(SUBSTR(start_tm, 4, 2) AS SIGNED) AS hour,
car_cls
FROM didi_sht_rcd
),
FilteredData AS (
SELECT *
FROM TimeExtracted
WHERE hour BETWEEN 18 AND 23
),
HourlyCounts AS (
SELECT
hour,
COUNT(*) AS total_orders,
SUM(CASE WHEN car_cls = 'A' THEN 1 ELSE 0 END) AS A_orders
FROM FilteredData
GROUP BY hour
),
PercentageAOrders AS (
SELECT
hour,
(A_orders * 100.0 / total_orders) AS percentage_A_orders
FROM HourlyCounts
),
AggregatedData AS (
SELECT
COUNT(*) AS n,
SUM(hour) AS sum_x,
SUM(percentage_A_orders) AS sum_y,
SUM(hour * percentage_A_orders) AS sum_xy,
SUM(hour * hour) AS sum_x2,
SUM(percentage_A_orders * percentage_A_orders) AS sum_y2
FROM PercentageAOrders
)
SELECT
cast((n * sum_xy - sum_x * sum_y) /
(SQRT(n * sum_x2 - sum_x * sum_x) * SQRT(n * sum_y2 - sum_y * sum_y)) as decimal(10,2)) AS R
FROM AggregatedData;
WITH TimeExtracted AS (
SELECT
cust_uid,
start_loc,
end_loc,
SUBSTR(start_tm, 1, 2) AS hour,
car_cls
FROM didi_sht_rcd
),
FilteredData AS (
SELECT *
FROM TimeExtracted
WHERE hour BETWEEN '18' AND '23'
),
HourlyCounts AS (
SELECT
hour,
COUNT(*) AS total_orders,
SUM(CASE WHEN car_cls = 'A' THEN 1 ELSE 0 END) AS A_orders
FROM FilteredData
GROUP BY hour
)
SELECT
hour,
A_orders,
total_orders,
cast((A_orders * 100.0 / total_orders) as decimal(10,2)) AS percentage_A_orders
FROM HourlyCounts
ORDER BY hour;
with all_cnt as(
select
d1.start_loc
,d1.end_loc
,l1.loc_ctg
,l2.loc_ctg as ctg2
,count(*) as cnt
from
didi_sht_rcd as d1
join
loc_nm_ctg as l1
on
d1.start_loc=l1.loc_nm
join
loc_nm_ctg as l2
on
d1.end_loc=l2.loc_nm
group by
3,4,1,2
),
rank_typ as(
select
*
,dense_rank()over(partition by loc_ctg,ctg2 order by cnt desc) as rnk
from
all_cnt
)
select
start_loc
,end_loc
,loc_ctg
,ctg2
,cnt
from
rank_typ
where
rnk=1
order by
cnt desc,
loc_ctg, ctg2;
with all_cnt as(
select
d1.start_loc
,d1.end_loc
,l1.loc_ctg
,l2.loc_ctg as ctg2
,count(*) as cnt
from
didi_sht_rcd as d1
join
loc_nm_ctg as l1
on
d1.start_loc=l1.loc_nm
join
loc_nm_ctg as l2
on
d1.end_loc=l2.loc_nm
group by
3,4,1,2
),
rank_typ as(
select
*
,row_number()over(partition by loc_ctg,ctg2 order by cnt desc) as rnk
from
all_cnt
)
select
start_loc
,end_loc
,loc_ctg
,ctg2
,cnt
from
rank_typ
where
rnk=1
ORDER BY
cnt DESC,
loc_ctg, ctg2;
with all_cnt as(
select
d1.start_loc
,d1.end_loc
,l1.loc_ctg
,l2.loc_ctg as ctg2
,count(*) as cnt
from
didi_sht_rcd as d1
join
loc_nm_ctg as l1
on
d1.start_loc=l1.loc_nm
join
loc_nm_ctg as l2
on
d1.end_loc=l2.loc_nm
group by
3,4,1,2
order by
5 desc
),
rank_typ as(
select
*
,row_number()over(partition by loc_ctg,ctg2 order by cnt desc) as rnk
from
all_cnt
)
select
start_loc
,end_loc
,loc_ctg
,ctg2
,cnt
from
rank_typ
where
rnk=1
with all_typ as
(select
d1.start_loc
,d1.end_loc
,l1.loc_ctg
,l2.loc_ctg as ctg2
,count(*) as cnt
from
didi_sht_rcd as d1
join
loc_nm_ctg as l1
on
d1.start_loc=l1.loc_nm
join
loc_nm_ctg as l2
on
d1.end_loc=l2.loc_nm
where
l1.loc_ctg='酒店'
or
l2.loc_ctg='酒店'
group by
3,4,1,2),
rank_typ as
(select
*
,row_number()over(partition by loc_ctg,ctg2 order by cnt desc) as rnk
from
all_typ
order by
cnt desc)
select
start_loc,
end_loc,
loc_ctg,
ctg2 as end_ctg
,cnt
from
rank_typ
where
rnk=1
with typ_cnt as(
select
d1.start_loc
,d1.end_loc
,l2.loc_ctg
,count(*) as cnt
from
didi_sht_rcd as d1
join
loc_nm_ctg as l1
on
d1.start_loc=l1.loc_nm
join
loc_nm_ctg as l2
on
d1.end_loc=l2.loc_nm
where
l1.loc_ctg='酒店'
group by
1,3,2
order by
4 desc
),
rnk_canguan as(
select
*
,row_number()over(partition by loc_ctg order by cnt desc) as rnk
from
typ_cnt
)
select
start_loc
,end_loc
,loc_ctg
,cnt
from
rnk_canguan
where
rnk=1
with typ_cnt as(
select
d1.start_loc
,d1.end_loc
,l2.loc_ctg
,count(*) as cnt
from
didi_sht_rcd as d1
join
loc_nm_ctg as l1
on
d1.start_loc=l1.loc_nm
join
loc_nm_ctg as l2
on
d1.end_loc=l2.loc_nm
where
l1.loc_ctg='酒店'
group by
1,3,2
order by
4 desc
),
rnk_canguan as(
select
*
,row_number()over(partition by start_loc,loc_ctg order by cnt desc) as rnk
from
typ_cnt
)
select
start_loc
,end_loc
,loc_ctg
,cnt
from
rnk_canguan
where
rnk=1
WITH hotel_routes AS (
SELECT r.start_loc, r.end_loc, l_end.loc_ctg, COUNT(*) AS trip_count
FROM didi_sht_rcd r
JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE l_start.loc_ctg = '酒店'
GROUP BY r.start_loc, r.end_loc, l_end.loc_ctg
),
ranked_routes AS (
SELECT start_loc, end_loc, loc_ctg, trip_count,
ROW_NUMBER() OVER (PARTITION BY loc_ctg ORDER BY trip_count DESC) AS route_rank
FROM hotel_routes
)
SELECT start_loc, end_loc, loc_ctg, trip_count
FROM ranked_routes
WHERE route_rank = 1
ORDER BY trip_count DESC;
WITH hotel_to_cafe AS (
SELECT r.start_loc, r.end_loc, COUNT(*) AS trip_count
FROM didi_sht_rcd r
JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
WHERE l_start.loc_ctg = '酒店'
GROUP BY r.start_loc, r.end_loc
)
SELECT start_loc, end_loc, trip_count
FROM hotel_to_cafe
ORDER BY trip_count DESC
LIMIT 1;
SELECT
r.start_loc
,r.end_loc
,count(1) as cnt
FROM didi_sht_rcd r
JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE l_start.loc_ctg = '酒店'
AND l_end.loc_ctg = '餐饮'
group by
1,2
order by
3 desc
limit
1
select
cust_uid
,d1.start_loc
,d1.end_loc
,start_tm
,car_cls
from
didi_sht_rcd as d1
join
loc_nm_ctg as t1
on
d1.start_loc=t1.loc_nm
join
loc_nm_ctg as t2
on
d1.end_loc=t2.loc_nm
where
t1.loc_ctg='住宅'
and
t2.loc_ctg='写字楼'
SELECT
t1.*
FROM
didi_sht_rcd t1
WHERE
EXISTS (
SELECT 1
FROM loc_nm_ctg t2
WHERE t2.loc_nm = t1.start_loc AND t2.loc_ctg = '餐饮'
)
AND
EXISTS (
SELECT 1
FROM loc_nm_ctg t3
WHERE t3.loc_nm = t1.end_loc AND t3.loc_ctg = '餐饮'
);
WITH ConvertedTime AS (
SELECT
order_id,
cust_uid,
DATE_ADD(call_time, INTERVAL -3 HOUR) AS local_call_time,
grab_time,
cancel_time,
finish_time
FROM
didi_order_rcd
),
CallDates AS (
SELECT
order_id,
cust_uid,
local_call_time,
DATE(local_call_time) AS call_date
FROM
ConvertedTime
),
NextDayCalls AS (
SELECT
c1.order_id AS order_id_1,
c1.call_date AS call_date_1,
c2.order_id AS order_id_2,
c2.call_date AS call_date_2
FROM
CallDates c1
JOIN
CallDates c2
ON
c2.call_date = DATE_ADD(c1.call_date, INTERVAL 1 DAY)
AND
c1.cust_uid = c2.cust_uid
AND
c1.order_id = c2.order_id
),
NextDayCallCount AS (
SELECT
COUNT(DISTINCT order_id_1) AS next_day_call_count
FROM
NextDayCalls
),
TotalOrderCount AS (
SELECT
COUNT(order_id) AS total_order_count
FROM
didi_order_rcd
where finish_time = '1970-01-01 00:00:00'
)
SELECT
ncc.next_day_call_count,
toc.total_order_count,
CONCAT(FORMAT((ncc.next_day_call_count * 1.0 / toc.total_order_count) * 100, 2), '%') AS next_day_call_ratio
FROM
NextDayCallCount ncc,
TotalOrderCount toc;
select hour(local_call_time) as local_hour, count(1) as cnt
from
(
SELECT
order_id,
cust_uid,
DATE_ADD(call_time, INTERVAL -3 HOUR) AS local_call_time,
grab_time,
cancel_time,
finish_time
FROM
didi_order_rcd
)t
group by hour(local_call_time) order by 2 desc
select
count(distinct order_id) as total_call
,count(distinct case when grab_time!='1970-01-01 00:00:00' THEN order_id end ) as grab_cnt
,concat(round(count(distinct case when grab_time!='1970-01-01 00:00:00' THEN order_id end )/count(distinct order_id)*100,2),'%') as answer_rate
from
didi_order_rcd
where
date(call_time)='2021-05-03'
SELECT
m_flg,
round(AVG(daily_views),2) AS avg_daily_views
FROM (
SELECT
t100.usr_id,
m_flg,
DATE(v_tm) AS view_date,
COUNT(v_id) AS daily_views
FROM
bilibili_t100 t100
JOIN
bilibili_t20 t20 ON t100.usr_id = t20.usr_id
WHERE
DATE(v_tm) >= '2021-02-01' AND DATE(v_tm) <= '2021-02-28'
GROUP BY
t100.usr_id, m_flg, view_date
) AS subquery
GROUP BY
m_flg;
我一开始想着用exist，可能是没掌握具体语法，导致没输出出来，后面懂逻辑了；想问一下，不关注代码量，只关注性能的话，exist是不是比join的消耗资源要少很多啊 SELECT t1.* FROM didi_sht_rcd t1 WHERE EXISTS ( SELECT 1 FROM loc_nm_ctg t2 WHERE t2.loc_nm = t1.start_loc AND t2.loc_ctg = '餐饮' ) AND EXISTS ( SELECT 1 FROM loc_nm_ctg t3 WHERE t3.loc_nm = t1.end_loc AND t3.loc_ctg = '餐饮' )我这个表格跟输出示例是一样的呀，答案里面没有保留2位小数 with total_typ_cnt as (select v_typ ,count(distinct usr_id) as total_user from bilibili_t20 as bt2 join bilibili_t3 as bt3 on bt2.v_id=bt3.v_id group by 1), usr_see_typ as ( select usr_id ,count(distinct v_typ) as typ_cnt from bilibili_t20 as bt2 join bilibili_t3 as bt3 on bt2.v_id=bt3.v_id group by 1 ) select t.v_typ, count(distinct case when typ_cnt >= 3 then u.usr_id end) as multi_category_viewers , t.total_user as total_viewers, round(count(distinct case when typ_cnt >= 3 then u.usr_id end)/ t.total_user*100,2) as mcv_index from bilibili_t20 as bt2 join bilibili_t3 as bt3 on bt2.v_id = bt3.v_id join usr_see_typ as u on bt2.usr_id = u.usr_id join total_typ_cnt as t on bt3.v_typ = t.v_typ group by t.v_typ, t.total_user order by 4 desc老师我觉得按照时间来划分梯队比较符合业务逻辑，但是最后计算出来的数字和答案不一样，我也不知道为什么 with userpurchase as (select user_id ,begin_date ,end_date ,pay_amount ,datediff(end_date,begin_date)+1 as duration ,case when datediff(end_date,begin_date)+1 between 20 and 32 then '月卡' when datediff(end_date,begin_date)+1 between 80 and 99 then '季卡' when datediff(end_date,begin_date)+1 between 170 and 190 then '半年卡' when datediff(end_date,begin_date)+1 >200 then '年卡' end as typ from bilibili_m1), time_2021 as ( select m_date ,y_m from bilibili_m2 where year(m_date)=2021 ), typ_income as (select user_id ,u.begin_date ,u.end_date ,pay_amount ,duration ,pay_amount/duration as daily_income ,typ ,t.m_date ,y_m from time_2021 as t join userpurchase as u on t.m_date between u.begin_date and u.end_date) select round(sum(case when typ='月卡' then daily_income end),2) as yue ,round(sum(case when typ='季卡' then daily_income end),2) as ji ,round(sum(case when typ='半年卡' then daily_income end),2) as bannian ,round(sum(case when typ='年卡' then daily_income end),2) as nian ,round(sum(daily_income),2) as total from typ_incomeselect area ,count(*) as total_companies ,count(case when (name like '%中国%' or name like '中%') then ts_code else null end) as chinese_named_companies ,round(count(case when (name like '%中国%' or name like '中%') then ts_code else null end )/count(*),3) as proportion from stock_info group by 1 order by 3 desc limit 5 这个地方的计算逻辑，我有点不太明白，代码就是计数统计那边有区别，结果差的很大select area ,count(*) as total_companies ,count(case when (name like '%中国%' or name like '中%') then ts_code else null end) as chinese_named_companies ,round(count(case when (name like '%中国%' or name like '中%') then ts_code else null end )/count(*),3) as proportion from stock_info group by 1 order by 4 desc ===按比例排序哦，你写成第3列了。 limit 5select t2.snd_usr_id as user_a ,t2.rcv_usr_id as user_b from tx_red_pkt_rcd as t1 left join tx_red_pkt_rcd as t2 on t2.snd_usr_id=t1.rcv_usr_id where t2.rcv_usr_id=t1.snd_usr_id and DATE(t1.snd_datetime) = '2021-02-13' AND DATE(t2.snd_datetime) = '2021-02-13' and t1.snd_usr_id<t1.rcv_usr_id group by 1,2 输出结果不对这个代码应该没有错呀 select count(case when date(snd_datetime)='2021-02-13' then snd_usr_id end ) as total_sent ,count(case when date(rcv_datetime)='1900-01-01' then snd_usr_id end ) as received_count ,100-round(count(case when date(rcv_datetime)='1900-01-01' then snd_usr_id end )/nullif(count(case when date(snd_datetime)='2021-02-13' then snd_usr_id end ),0)*100,2) as receive_rate from tx_red_pkt_rcd不知道为什么错 WITH user_login_days AS ( select usr_id ,date(login_time) as login_date from user_login_log where login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)), rnk_user as( select usr_id ,login_date ,row_number()over(partition by usr_id order by login_date) as rnk from user_login_days ), rnk_sub as( select usr_id ,login_date ,rnk ,date_sub(login_date,interval rnk day) as date_rnk from rnk_user ), all_usr as( select usr_id ,date_rnk ,count(*) as 3_day_active from rnk_sub group by 1,2 having 3_day_active>=3) select usr_id ,date_rnk ,date_add(date_rnk,interval 3_day_active day) as end_date ,3_day_active from all_usrselect f.mch_id ,count(distinct f.cust_uid) ,count(distinct p.cust_uid) ,round(count(distinct p.cust_uid)/count(distinct f.cust_uid)*100,2) from xhs_fav_rcd as f left join xhs_pchs_rcd as p on p.cust_uid=f.cust_uid and p.mch_id=f.mch_id and datediff(f.fav_tm,p.pchs_tm)<0 group by 1 order by 4 desc 还是有点迷糊，这一块，我的第一列应该没问题，但是第二列的问题不知道怎么解决select count(distinct f.cust_uid) as conversion_user_count from xhs_fav_rcd as f left join xhs_pchs_rcd as p on f.cust_uid=p.cust_uid and f.mch_id=p.mch_id and datediff(f.fav_tm,p.pchs_tm)<0 这个地方如果在表连接里面使用时间判断，左表有数据，右表没有数据，但是最后这条记录还是会保存下来对吧select concat(year(purchase_time),lpad(week(purchase_time),2,'0')) as week_number ,case when product_type='iPhone' then product_type else 'Not iPhone' end as category ,count(distinct user_id) as user_count from apple_pchs_rcd group by 1,2 order by 1,2 跟答案只有输出星期那一列有区别，但为什么数量差别这么大不懂我的代码为什么运行不出来答案那个结果 select date(login_time) ,count(*) as cnt from user_login_log where date(login_time) between '2024-09-01' and '2024-09-30' and (usr_id,login_time)in (select usr_id ,min(login_time) from user_login_log group by 1) group by 1 order by 1我知道要输出各个时间段的人，而不是登录用户数量，但是我有个疑问，通勤是两个时间段，那么早上有通勤记录，跟晚上有通勤记录，应该算作两次吧（单纯从理解这块的入手的话） with login_typ as ( SELECT usr_id, CASE WHEN TIME(login_time) BETWEEN '07:30:00' AND '09:30:00' OR TIME(login_time) BETWEEN '18:30:00' AND '20:30:00' THEN 'commute' WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN 'break' WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' OR TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' THEN 'bedtime' ELSE NULL END AS login_typ FROM user_login_log WHERE EXTRACT(YEAR FROM login_time) = EXTRACT(YEAR FROM CURRENT_DATE) AND EXTRACT(MONTH FROM login_time) = EXTRACT(MONTH FROM CURRENT_DATE - INTERVAL 1 MONTH) ) select sum(case when login_typ='commute' then 1 else 0 end ) as commute ,sum(case when login_typ='break' then 1 else 0 end ) as lunch_break ,sum(case when login_typ='bedtime' then 1 else 0 end ) as bedtime from login_typ老师我想知道怎么才能不漏掉两类股票都没上市的年份。难道是我还需要自己生成一个新表表述2000-2024年吗 with fillter_stock as ( select ts_code ,symbol ,name ,area ,case when industry='软件服务' then industry else '地产' end as industry ,list_date from stock_info where (year(list_date) between '2000' and '2024') and industry rlike '地产|软件服务'), done_stock as( select year(list_date) as Y ,industry ,count(industry) as stock_cnt from fillter_stock group by year(list_date) ,industry ) select Y ,max(case when industry='地产' then stock_cnt else 0 end) as '地产' ,max(case when industry='软件服务' then stock_cnt else 0 end) as '软件服务' from done_stock group by 1 order by 1c如果是0了，那就是一个点啊。 select * from numbers_for_fun where a=0 and c!=0 and (b=1 or b=-1) order by id我其实不是特别理解有两个点： 1、我这个代码输出的结果里面，第一行是null。但是我临时表1是输出的不同的2023月份，第二章合并表用的inner join，第三个临时表只是新增一列，应该不会出现null值 2、我第一个表中distinct 月份，然后第三章表进行左连接，4月份还掉了？按理来说date_table应该是完整的月份呀，不会出现这么大的误差的。 with date_summary as ( select distinct date_format(date_value,'%Y-%m') as trx_mon from date_table where year(date_value)=2023 ), monthly_sum_trx_amt as ( select substr(trx_time,1,7) as trx_mon ,sum(trx_amt) as month_trx_amt from cmb_usr_trx_rcd as u inner join cmb_mch_typ as m using (mch_nm) where usr_id=5201314520 and mch_typ='休闲娱乐' and year(trx_time)=2023 group by substr(trx_time,1,7) ), total_sum_trx_amt as ( select m1.trx_mon ,coalesce(month_trx_amt,0) as month_trx_amt ,sum(month_trx_amt)over(order by m1.trx_mon ) as trx_amt from date_summary as d1 left join monthly_sum_trx_amt as m1 on d1.trx_mon=m1.trx_mon ) select trx_mon ,trx_amt from total_sum_trx_amt order by trx_monwith math_rank as ( select student_id ,case when score>=110 then '[110, 120]' when score between 90 and 109 then '[90, 110)' when score between 60 and 89 then '[60, 90)' else '[0, 60)' end as score_range from scores where exam_date='2024-06-30' and subject='数学') select score_range ,count(*) as num_students from math_rank group by score_range 这个地方我输出的结果也是一样的，但是跟他排序不同。我想知道在这种情况下需要怎么排序，才能排成答案这种。select name ,subject ,class_code ,qualification from teachers where fir_degr rlike '北京大学|清华大学' and head_teacher is not null order by name asc 我用正则表达式进行匹配为什么不对select st.student_id ,st.name ,sc.score ,row_number()over(order by sc.score desc) as rnk from students as st inner join scores as sc on st.student_id=sc.student_id where st.grade_code='S1' and sc.subject='物理' ORDER BY rnk,st.student_id limit 10 就我这个也能输出排名前十，但是跟答案不一样，我筛选的条件也是s1学物理的select substr(DATE_FORMAT(trx_time, '%Y-%m-%d'),1,7) as trx_mon ,max(last_day(trx_time)) as last_day from cmb_usr_trx_rcd where year(trx_time) in (2023,2024) group by substr(DATE_FORMAT(trx_time, '%Y-%m-%d'),1,7) order by trx_mon 我想问一个语法方面的问题，就是有了group分组之后，是不是输出的列也是要聚合好的。我一开始lastday那里没有加max，就是无法输出。select * from stock_info where (list_date between '2026-06-01' and '2006-09-01') and industry ='银行' and area='北京' order by list_date 不懂为啥没有输出select trx_amt ,count(trx_amt) as total_trx_cnt ,count(distinct usr_id) as unique_usr_cnt ,count(trx_amt)/count(distinct usr_id) as avg_trx_per_user from cmb_usr_trx_rcd where mch_nm='红玫瑰按摩保健休闲' and year(trx_time)=2023 and month(trx_time) in (1,2,3,4,5,6,7,8,9,10,11,12) or year(trx_time)=2024 and month(trx_time) in (1,2,3,4,5,6) group by trx_amt order by avg_trx_per_user desc limit 5 我输出的结果中total_trx_cnt 数量不对，288.888,1288都多了两个