右下角图片

排名

用户解题统计

过去一年提交了

勋章 ①金银铜:在竞赛中获得第一二三名;②好习惯:自然月10天提交;③里程碑:解决1/2/5/10/20/50/100/200题;④每周打卡挑战:完成每周5题,每年1月1日清零。

收藏

收藏日期 题目名称 解决状态
2025-01-09 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-01-09 一线城市历年平均气温 
我知道我的问题出现在哪里了。
我一直以为 format( col ,2 ) 和 cast( col as decimal(4,2 ) ) 的结果是完全一样的,其实不对。
问题出在 2013 年 shenzhen 原本的值是 23.125 分别用这两个公式一个 是 23.12 一个是 23.13.后面探究了一下

SELECT cast('23.125' as decimal(4,2 )); -- 23.13
SELECT cast(23.125 as decimal(4,2 ));  -- 23.13
SELECT  format('23.125',2); -- 23.12
SELECT  format(23.125,2); -- 23.13

我原本的脚本在传递给 FORMAT 函数的是一个字符串而不是数字。MySQL 首先需要将这个字符串转换成一个数值类型来处理这种隐式转换在某些环境或者并不可靠。

而且我发现一个 有意思的事情 format('23.125',2):
当倒数第2位是奇数 比如 23.115,23.135,23.155 ... 结果会四舍五入 即 23.12,23.14,23.16
当倒数第2位是偶数 比如 23.125,23.145,23.165 ... 结果不会四舍五入 也是 23.12,23.14,23.16

估计是因为隐式转换的原因,具体的我没有搞明白,希望有看到这里的大佬可以解惑一下!
你的研究很仔细哈哈哈。舍入目的还是建议使用round,mysql一直有点疯,不太稳定。
2025-01-09 一线城市历年平均气温 
输出的是和输出示例是一样的呀,问题在哪?
select 
	year(dt) as Y
	, format(avg(REGEXP_SUBSTR(if(city = 'beijing', tmp_h, null), '-?[0-9]+')), 2)as beijing
	, format(avg(REGEXP_SUBSTR(if(city = 'shanghai', tmp_h, null), '-?[0-9]+')), 2)as shanghai
	, format(avg(REGEXP_SUBSTR(if(city = 'shenzhen', tmp_h, null), '-?[0-9]+')), 2)as shenzhen
	, format(avg(REGEXP_SUBSTR(if(city = 'guangzhou', tmp_h, null), '-?[0-9]+')), 2)as guangzhou
from weather_rcd_china
where 
	year(dt) between 2011 and 2022
	and city in ('beijing','shanghai','shenzhen','guangzhou')
group by 1
order by 1
啥也没说
2025-01-09 城市平均最高气温 
ROUND() 函数本身不会保证总是显示两位小数
可以使用 FORMAT() 函数或 DECIMAL 数据类型来控制输出格式
啥也没说
2025-01-09 滴滴面试真题(3)UTC转化后的本地时间呼叫高峰期 
提交不对呢,参考答案提交也不对
参考答案当然不对咯,毕竟只能参考嘛 巴西跟UTC时间到底差了多少呢?
2025-01-09 快手面试真题(3)同时在线人数峰值时点 
首先输出实例和提议不符,我做的结果和参考答案是一样的,提交是错误,参考答案提交也是错误
已修改,可以再试试
2025-01-09 快手面试真题(2)同时在线人数峰值 
记录一下第一次算峰值,想了很久,不知道还有没有其他的简单方法
with data1 as(
	select usr_id, live_id, enter_time as time1, 1 as flag1
	from ks_live_t1
	union all
	select usr_id, live_id, leave_time as time1, -1 as flag1
	from ks_live_t1
), 
data2 as(
	select 
		live_id
		, sum(flag1) over(partition by live_id order by time1 ) as online_users
	from data1
)
select t1.live_id,t2.live_nm, max(online_users) as max_online_users
from data2 t1 left join ks_live_t2 t2 on t1.live_id = t2.live_id
group by 1, 2
order by 3 desc
啥也没说
2025-01-09 10月1日后再也没活跃过的用户 
如果活跃的定义就是登录的话,那么只需要判断最后一次登录时间是否 <=2024-10-01 不就可以吗
select count(1) as inactive_user_count
from(
	select usr_id, max(login_time)
	from user_login_log
	group by 1
	having max(login_time) <= '2024-10-01'
) tmp
同样妙不可言
2025-01-09 窗口函数(4)越来越喜欢召妓,窗口函数用累计(2) 
答案只包含2023的记录,没有2024,建议修改题目或答案
啥也没说
2025-01-09 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数 
需求描述是:消费笔数Top1 题目又是前二,相互间不一致,建议统一
已改,笔芯!
2025-01-08 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
输出示例表格的第一个字段 rx_mon 前面少了字母t 容易误导
啥也没说
2025-01-08 小结(1)大数据早就能扫黄,找足证据不慌张 
问一下 区间限定[23:00:00, 03:00:00) 为什么答案 between 0 and 3 不是 between 0 and 2 呀?
啥也没说
2025-01-08 小结(1)大数据早就能扫黄,找足证据不慌张 
题目给定时间区间[23:00:00, 03:00:00) 为什么要 between 0 and 3 不应该  between 0 and 2 吗
已修改~

提交记录

提交日期 题目名称 提交代码
2025-01-09 一线城市历年平均气温 
select 
	year(dt) as Y
	, cast(avg(REGEXP_SUBSTR(if(city = 'beijing', tmp_h, null), '-?[0-9]+'))as decimal(4, 2))as beijing
	, cast(avg(REGEXP_SUBSTR(if(city = 'shanghai', tmp_h, null), '-?[0-9]+'))as decimal(4, 2))as shanghai
	, cast(avg(REGEXP_SUBSTR(if(city = 'shenzhen', tmp_h, null), '-?[0-9]+'))as decimal(4, 2))as shenzhen
	, cast(avg(REGEXP_SUBSTR(if(city = 'guangzhou', tmp_h, null), '-?[0-9]+'))as decimal(4, 2))as guangzhou
from weather_rcd_china
where 
	year(dt) between 2011 and 2022
	and city in ('beijing','shanghai','shenzhen','guangzhou')
group by 1
order by 1
2025-01-09 深圳气温异常年份 
select 
	year(dt) as `year`
	,cast(avg(REGEXP_SUBSTR(tmp_h, '-?[0-9]+')) as decimal(4, 2))as avg_tmp_h
	,CASE 
WHEN ABS(AVG(CAST(REGEXP_SUBSTR(tmp_h, '-?[0-9]+') AS DECIMAL)) - 
LAG(AVG(CAST(REGEXP_SUBSTR(tmp_h, '-?[0-9]+') AS DECIMAL)), 1) OVER (ORDER BY year(dt))) >= 1 
THEN 'Yes' 
ELSE 'No' 
END as significant_change
from weather_rcd_china
where 
	year(dt) between 2011 and 2022
	and city = 'shenzhen'
group by 1
order by 1
2025-01-09 深圳气温异常年份 
WITH yearly_avg AS (
    SELECT 
        city,
        YEAR(dt) AS year,
        AVG(REPLACE(tmp_h, '℃', '') + 0) AS avg_high_temp
    FROM weather_rcd_china
    WHERE city = 'shenzhen'
      AND dt BETWEEN '2011-01-01' AND '2022-12-31'
    GROUP BY city, YEAR(dt)
),
yearly_avg_with_lag AS (
    SELECT 
        city,
        year,
        avg_high_temp,
        LAG(avg_high_temp) OVER (ORDER BY year) AS prev_year_avg_temp
    FROM yearly_avg
),
temp_changes AS (
    SELECT 
        year,
        avg_high_temp,
        prev_year_avg_temp,
        (avg_high_temp - COALESCE(prev_year_avg_temp, 0)) AS temp_change
    FROM yearly_avg_with_lag
)
SELECT 
    year,
    cast(avg_high_temp as decimal(10,2)) as avg_tmp_h,
    CASE 
        WHEN ABS(avg_high_temp - COALESCE(prev_year_avg_temp, 0)) >=1 THEN 'Yes'
        ELSE 'No'
    END AS significant_change
FROM temp_changes
WHERE year BETWEEN 2011 AND 2022
ORDER BY year;
2025-01-09 深圳气温异常年份 
select 
	year(dt) as `year`
	, format(avg(REGEXP_SUBSTR(tmp_h, '-?[0-9]+')), 2)as avg_tmp_h
	,CASE 
WHEN ABS(AVG(CAST(REGEXP_SUBSTR(tmp_h, '-?[0-9]+') AS DECIMAL)) - 
LAG(AVG(CAST(REGEXP_SUBSTR(tmp_h, '-?[0-9]+') AS DECIMAL)), 1) OVER (ORDER BY year(dt))) >= 1 
THEN 'Yes' 
ELSE 'No' 
END as significant_change
from weather_rcd_china
where 
	year(dt) between 2011 and 2022
	and city = 'shenzhen'
group by 1
order by 1
2025-01-09 一线城市历年平均气温 
select 
	year(dt) as Y
	, format(avg(REGEXP_SUBSTR(if(city = 'beijing', tmp_h, null), '-?[0-9]+')), 2)as beijing
	, format(avg(REGEXP_SUBSTR(if(city = 'shanghai', tmp_h, null), '-?[0-9]+')), 2)as shanghai
	, format(avg(REGEXP_SUBSTR(if(city = 'shenzhen', tmp_h, null), '-?[0-9]+')), 2)as shenzhen
from weather_rcd_china
where 
	year(dt) between 2011 and 2022
	and city in ('beijing','shanghai','shenzhen','guangzhou')
group by 1
order by 1
2025-01-09 一线城市历年平均气温 
select 
	year(dt) as Y
	, format(avg(REGEXP_SUBSTR(if(city = 'beijing', tmp_h, null), '-?[0-9]+')), 2)as beijing
	, format(avg(REGEXP_SUBSTR(if(city = 'shanghai', tmp_h, null), '-?[0-9]+')), 2)as shanghai
	, format(avg(REGEXP_SUBSTR(if(city = 'shenzhen', tmp_h, null), '-?[0-9]+')), 2)as shenzhen
	, format(avg(REGEXP_SUBSTR(if(city = 'guangzhou', tmp_h, null), '-?[0-9]+')), 2)as '广州'
from weather_rcd_china
where 
	year(dt) between 2011 and 2022
	and city in ('beijing','shanghai','shenzhen','guangzhou')
group by 1
order by 1
2025-01-09 一线城市历年平均气温 
select 
	year(dt) as Y
	, format(avg(REGEXP_SUBSTR(if(city = 'beijing', tmp_h, null), '-?[0-9]+')), 2)as beijing
	, format(avg(REGEXP_SUBSTR(if(city = 'shanghai', tmp_h, null), '-?[0-9]+')), 2)as shanghai
	, format(avg(REGEXP_SUBSTR(if(city = 'shenzhen', tmp_h, null), '-?[0-9]+')), 2)as shenzhen
	, format(avg(REGEXP_SUBSTR(if(city = 'guangzhou', tmp_h, null), '-?[0-9]+')), 2)as guangzhou
from weather_rcd_china
where 
	year(dt) between 2011 and 2022
	and city in ('beijing','shanghai','shenzhen','guangzhou')
group by 1
order by 1
2025-01-09 冬季下雪天数 
select 
	city
	, sum(case when con like '%雪%' then 1 else 0 end) as snowy_days
from weather_rcd_china
where month(dt) in (12, 1, 2)
group by 1
order by 2 desc
2025-01-09 多云天气天数 
select 
	city
	, sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days
	, concat(format((sum(case when con like '%多云%' then 1 else 0 end) / count(1)) * 100, 2), '%') as P
from weather_rcd_china
where year(dt) = '2021'
group by 1
order by 3 desc
2025-01-09 城市平均最高气温 
select 
	city
	, format(avg(REGEXP_SUBSTR(tmp_h, '-?[0-9]+')), 2)as avg_tmp_h
from weather_rcd_china
where year(dt) = '2021'
group by 1
order by 2 desc
2025-01-09 滴滴面试真题(3)UTC转化后的本地时间呼叫高峰期 
SELECT 
    HOUR(local_call_time) AS local_hour,  
    COUNT(1) AS cnt 
FROM 
(
    SELECT 
        order_id,
        cust_uid,
        DATE_ADD(call_time, INTERVAL -4 HOUR) AS local_call_time, 
        grab_time,
        cancel_time,
        finish_time
    FROM 
        didi_order_rcd 
) t
GROUP BY 
    HOUR(local_call_time)  
ORDER BY 
    cnt DESC
2025-01-09 滴滴面试真题(3)UTC转化后的本地时间呼叫高峰期 
select hour(local_time) as local_time, count(1) as cnt
from (
	selectCONVERT_TZ(call_time, '+00:00', '-04:00') AS local_time
	from didi_order_rcd
) t1
group by 1
order by 2 desc
2025-01-09 滴滴面试真题(3)UTC转化后的本地时间呼叫高峰期 
select hour(local_time) as local_time, count(1) as cnt
from (
	selectCONVERT_TZ(call_time, '+00:00', '-04:00') AS local_time
	from didi_order_rcd
) t1
group by 1
order by 2 desc, 1
2025-01-09 滴滴面试真题(1)-打车订单应答率 
select 
	count(1) as total_orders
	, count(grab_time >= call_time or null ) as answered_orders
	, concat(round((count(grab_time >= call_time or null ) / count(1))*100, 2), '%') as answer_rate
from didi_order_rcd
where 
	substring(call_time,1,10) = '2021-05-03'
2025-01-09 快手面试真题(3)同时在线人数峰值时点 
WITH UserActivity AS (
    SELECT 
        t1.usr_id,
        t1.live_id,
        t1.enter_time AS event_time,
        1 AS act
    FROM 
        ks_live_t1 t1
    UNION ALL
    SELECT 
        t1.usr_id,
        t1.live_id,
        t1.leave_time AS event_time,
        -1 AS act
    FROM 
        ks_live_t1 t1
),
CumulativeOnline AS (
    SELECT 
        live_id,
        event_time,
        SUM(act) OVER (PARTITION BY live_id ORDER BY event_time) AS online_users
    FROM 
        UserActivity
),
PeakOnline AS (
    SELECT 
        live_id,
        event_time,
        online_users,
        MAX(online_users) OVER (PARTITION BY live_id) AS max_online_users
    FROM 
        CumulativeOnline
),
FirstLastPeak AS (
    SELECT 
        live_id,
        min(event_time) AS first_peak_time,
        max(event_time) AS last_peak_time,
        max_online_users
    FROM 
        PeakOnline
    WHERE 
        online_users = max_online_users
    GROUP BY 
        live_id, max_online_users
)
SELECT 
    flp.live_id,
    t2.live_nm,
    flp.max_online_users,
    flp.first_peak_time,
    flp.last_peak_time
FROM 
    FirstLastPeak flp
JOIN 
    ks_live_t2 t2
ON 
    flp.live_id = t2.live_id
ORDER BY 
    flp.max_online_users DESC;
2025-01-09 快手面试真题(3)同时在线人数峰值时点 
with data1 as(
	select usr_id, live_id, enter_time as time1, 1 as flag1
	from ks_live_t1
	union all
	select usr_id, live_id, leave_time as time1, -1 as flag1
	from ks_live_t1
), 
data2 as(
	select 
		live_id
		, sum(flag1) over(partition by live_id order by time1 ) as online_users
	from data1
)
select t1.live_id,t2.live_nm, max(online_users) as max_online_users
from data2 t1 left join ks_live_t2 t2 on t1.live_id = t2.live_id
group by 1, 2
order by 3 desc
2025-01-09 快手面试真题(3)同时在线人数峰值时点 
with data1 as(
	select usr_id, live_id, enter_time as time1, 1 as flag1
	from ks_live_t1
	union all
	select usr_id, live_id, leave_time as time1, -1 as flag1
	from ks_live_t1
), 
data2 as(
	select 
		live_id
		, time1
		, sum(flag1) over(partition by live_id order by time1 ) as online_users
	from data1
), 
data3 as(
	select live_id, max(online_users) as max_online_users
	from data2 
	group by 1
)
select 
	t1.live_id
	, t2.live_nm
	, t3.max_online_users as max_online_users
	, min(t1.time1) as first_peak_time
	, max(t1.time1) as last_peak_time
from data2 t1 left join ks_live_t2 t2 on t1.live_id = t2.live_id left join data3 t3 on t1.live_id = t3.live_id
where t1.online_users = t3.max_online_users
group by 1, 2, 3
order by 3 desc
2025-01-09 快手面试真题(2)同时在线人数峰值 
with data1 as(
	select usr_id, live_id, enter_time as time1, 1 as flag1
	from ks_live_t1
	union all
	select usr_id, live_id, leave_time as time1, -1 as flag1
	from ks_live_t1
), 
data2 as(
	select 
		live_id
		, sum(flag1) over(partition by live_id order by time1 ) as online_users
	from data1
)
select t1.live_id,t2.live_nm, max(online_users) as max_online_users
from data2 t1 left join ks_live_t2 t2 on t1.live_id = t2.live_id
group by 1,2
order by 3 desc
2025-01-09 快手面试真题(1)同时在线人数 
select t1.live_id, t2.live_nm, online_users
from (
select
	live_id
	, count(distinct usr_id) as online_users
from ks_live_t1
where enter_time <= '2021-09-12 23:48:38' and leave_time >= '2021-09-12 23:48:38'
group by 1
) t1 left join ks_live_t2 t2 on t1.live_id = t2.live_id
order by 3 desc,1
2025-01-09 快手面试真题(1)同时在线人数 
select t1.live_id, t2.live_nm, online_users
from (
select
	live_id
	, count(distinct usr_id) as online_users
from ks_live_t1
where enter_time <= '2021-09-12 23:48:38' and leave_time >= '2021-09-12 23:48:38'
group by 1
) t1 left join ks_live_t2 t2 on t1.live_id = t2.live_id
order by 3 desc,1 desc