北京网站seo收费标准,运营工资一般多少,徐州网站排名系统,鞍山外国网站制作#x1f34b;#x1f34b;大数据学习#x1f34b;#x1f34b;
#x1f525;系列专栏#xff1a; #x1f451;哲学语录: 用力所能及#xff0c;改变世界。 #x1f496;如果觉得博主的文章还不错的话#xff0c;请点赞#x1f44d;收藏⭐️留言#x1f4…大数据学习
系列专栏 哲学语录: 用力所能及改变世界。 如果觉得博主的文章还不错的话请点赞收藏⭐️留言支持一下博主哦 一、分组排序问题Top N 变体
1. 按多个条件排序并取 Top N
问题查询每个部门薪资最高且入职最早的前 2 名员工。 思路
窗口函数中用 ORDER BY salary DESC, hire_date ASC 实现多条件排序。用 ROW_NUMBER() 生成唯一排名避免并列。
代码模板
WITH ranked_employees AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, hire_date ASC) AS rankFROM employees
)
SELECT * FROM ranked_employees WHERE rank 2;2. 动态 Top N按分组比例取前 N%
问题查询每个部门薪资前 10% 的员工。 思路
用 NTILE(10) 将数据按薪资分为 10 组取第 1 组。
代码模板
WITH salary_tiles AS (SELECT *,NTILE(10) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_tileFROM employees
)
SELECT * FROM salary_tiles WHERE salary_tile 1;二、连续区间问题变体
1. 连续缺失值检测
问题检测用户登录记录中连续缺失超过 3 天的区间。 思路
生成完整日期序列左连接实际记录标记缺失日期。用 日期-行号 分组连续缺失区间。
代码模板
WITH all_dates AS (-- 生成日期序列略
),
missing_dates AS (SELECT user_id,date,CASE WHEN login_id IS NULL THEN 1 ELSE 0 END AS is_missingFROM all_datesLEFT JOIN user_logins USING (user_id, date)
),
missing_groups AS (SELECT user_id,date,DATE_SUB(date, ROW_NUMBER() OVER (PARTITION BY user_id, is_missing ORDER BY date)) AS grpFROM missing_datesWHERE is_missing 1
)
SELECT user_id,MIN(date) AS start_date,MAX(date) AS end_date,COUNT(*) AS missing_days
FROM missing_groups
GROUP BY user_id, grp
HAVING COUNT(*) 3;2. 周期性行为识别
问题识别用户每周固定某天的登录习惯如每周三。 思路
用 DAYOFWEEK() 获取星期几按用户和星期分组统计频次。
代码模板
SELECT user_id,DAYOFWEEK(login_date) AS day_of_week,COUNT(*) AS login_count,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) AS rank
FROM user_logins
GROUP BY user_id, DAYOFWEEK(login_date)
HAVING rank 1; -- 取频次最高的一天三、复杂聚合问题
1. 分组内条件聚合加权平均
问题计算每个商品在不同促销活动下的加权平均销量权重为活动持续天数。 思路
用 SUM(销量*权重)/SUM(权重) 实现加权平均。
代码模板
SELECT product_id,SUM(sales * duration_days) / SUM(duration_days) AS weighted_avg_sales
FROM (SELECT product_id,campaign_id,SUM(daily_sales) AS sales,DATEDIFF(end_date, start_date) 1 AS duration_daysFROM sales_recordsGROUP BY product_id, campaign_id, start_date, end_date
) t
GROUP BY product_id;2. 动态区间聚合按事件触发
问题计算用户每次登录后 24 小时内的消费总额。 思路
用 JOIN 关联同一用户的登录和消费记录筛选时间窗口。
代码模板
SELECT l.user_id,l.login_time,SUM(o.amount) AS total_spent
FROM user_logins l
LEFT JOIN orders o
ON l.user_id o.user_id
AND o.order_time BETWEEN l.login_time AND DATE_ADD(l.login_time, 1)
GROUP BY l.user_id, l.login_time;四、多维分析OLAP 风格
1. 小计与总计GROUPING SETS/CUBE/ROLLUP
问题同时计算按部门、职位和两者组合的薪资总和。 思路
用 GROUPING SETS 生成多种分组组合。
代码模板
SELECT dept_id,position,SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS((dept_id, position), -- 部门职位分组(dept_id), -- 部门分组(position), -- 职位分组() -- 总计
);2. 同比 / 环比跨时间周期比较
问题计算 2023 年每月销售额的同比和环比增长率。 思路
用 LAG() 获取上月 / 去年同月数据或用 JOIN 关联时间偏移表。
代码模板
WITH monthly_sales AS (SELECT YEAR(sale_date) AS sale_year,MONTH(sale_date) AS sale_month,SUM(amount) AS total_amountFROM salesGROUP BY YEAR(sale_date), MONTH(sale_date)
)
SELECT curr.sale_year,curr.sale_month,curr.total_amount,prev_month.total_amount AS prev_month_amount,prev_year.total_amount AS prev_year_amount,(curr.total_amount - prev_month.total_amount) / prev_month.total_amount AS mom_growth,(curr.total_amount - prev_year.total_amount) / prev_year.total_amount AS yoy_growth
FROM monthly_sales curr
LEFT JOIN monthly_sales prev_month
ON curr.sale_year prev_month.sale_year
AND curr.sale_month prev_month.sale_month 1
LEFT JOIN monthly_sales prev_year
ON curr.sale_year prev_year.sale_year 1
AND curr.sale_month prev_year.sale_month;五、地理信息与空间分析
1. 区域聚合按地理边界统计
问题统计每个城市商圈内的店铺数量。 思路
用 ST_Contains() 判断点店铺是否在多边形商圈内。
代码模板
SELECT district_name,COUNT(shop_id) AS shop_count
FROM shops s
JOIN districts d
ON ST_Contains(ST_GeomFromText(d.polygon_wkt), -- 商圈多边形ST_Point(s.longitude, s.latitude) -- 店铺坐标
)
GROUP BY district_name;2. 距离最近点查询
问题为每个用户找到距离最近的 3 个服务点。 思路
用 Haversine 公式计算距离ROW_NUMBER() 取 Top N。
代码模板
WITH distances AS (SELECT u.user_id,s.service_id,6371 * 2 * ASIN(SQRT(POWER(SIN((s.lat - u.lat) * PI()/180 / 2), 2) COS(u.lat * PI()/180) * COS(s.lat * PI()/180) *POWER(SIN((s.lon - u.lon) * PI()/180 / 2), 2))) AS distance_kmFROM users uCROSS JOIN service_points s
)
SELECT *
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY distance_km) AS rankFROM distances
) t
WHERE rank 3;六、时间窗口滑动聚合
1. 固定窗口聚合每小时 / 每天
问题计算每小时的平均请求数。 思路
用 DATE_TRUNC() 截断时间到小时按小时分组。
代码模板
SELECT DATE_TRUNC(HOUR, request_time) AS hour,COUNT(request_id) AS request_count,AVG(response_time) AS avg_response_time
FROM requests
GROUP BY DATE_TRUNC(HOUR, request_time);2. 滑动窗口聚合过去 N 条记录
问题计算每个用户最近 5 次登录的平均停留时长。 思路
用 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW 定义滑动窗口。
代码模板
SELECT user_id,login_time,session_duration,AVG(session_duration) OVER (PARTITION BY user_id ORDER BY login_time ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS avg_last_5_sessions
FROM user_sessions;七、数据透视与交叉表
1. 动态列转置不确定列数
问题将用户标签每行一个标签转为列每个标签一列。 思路
用 collect_set() 聚合标签size() 判断是否存在。
代码模板
WITH user_tags AS (SELECT user_id,collect_set(tag) AS tagsFROM user_tag_mappingGROUP BY user_id
)
SELECT user_id,CASE WHEN vip IN (SELECT * FROM UNNEST(tags)) THEN 1 ELSE 0 END AS is_vip,CASE WHEN new IN (SELECT * FROM UNNEST(tags)) THEN 1 ELSE 0 END AS is_new,-- 动态添加更多标签判断
FROM user_tags;2. 交叉表统计多维度组合
问题统计不同年龄段和性别用户的消费金额分布。 思路
用 CASE WHEN 组合维度SUM() 聚合金额。
代码模板
SELECT age_group,SUM(CASE WHEN gender M THEN amount ELSE 0 END) AS male_amount,SUM(CASE WHEN gender F THEN amount ELSE 0 END) AS female_amount,SUM(amount) AS total_amount
FROM users u
JOIN orders o USING (user_id)
GROUP BY age_group;八、递归查询与层级结构
1. 树形结构路径查询如组织架构
问题查询员工及其所有上级的完整路径。 思路
用递归 CTE 逐层向上查找上级。
代码模板
WITH RECURSIVE employee_hierarchy AS (SELECT emp_id,manager_id,emp_name,CAST(emp_name AS STRING) AS pathFROM employeesWHERE manager_id IS NULL -- 根节点CEOUNION ALLSELECT e.emp_id,e.manager_id,e.emp_name,CONCAT(eh.path, - , e.emp_name) AS pathFROM employees eJOIN employee_hierarchy eh ON e.manager_id eh.emp_id
)
SELECT * FROM employee_hierarchy;2. 层级聚合如区域销售额汇总
问题计算每个区域及其子区域的总销售额。 思路
自下而上递归聚合用 SUM() OVER (PARTITION BY region_id)。
代码模板
WITH region_sales AS (-- 基础销售额略
),
region_hierarchy AS (-- 区域层级关系略
),
recursive_sales AS (-- 递归计算子区域销售额略
)
SELECT region_id,region_name,SUM(sales_amount) OVER (PARTITION BY region_id) AS total_sales
FROM recursive_sales;九、字符串与模式匹配
1. 复杂字符串分割与提取
问题从日志中提取 user_id 和 action格式[user_id:1001][action:click]。 思路
用 regexp_extract() 或 substr()instr() 提取子串。
代码模板
SELECT regexp_extract(log_line, \\[user_id:(\\d)\\], 1) AS user_id,regexp_extract(log_line, \\[action:(\\w)\\], 1) AS action
FROM logs;2. 字符串相似度计算
问题找出商品名称中包含特定关键词的记录。 思路
用 LIKE 或 REGEXP 匹配或用 levenshtein_distance() 计算编辑距离。
代码模板
-- 方法1模糊匹配
SELECT * FROM products WHERE product_name LIKE %关键词%;-- 方法2正则匹配
SELECT * FROM products WHERE product_name REGEXP 关键词;-- 方法3相似度计算
SELECT *
FROM products
WHERE levenshtein_distance(product_name, 目标名称) 3;解题思路
问题拆解将复杂需求分解为子问题如 “连续登录” → “生成连续标识” → “分组统计”。数据建模明确输入输出表结构确定关联字段和聚合维度。技术选型 窗口函数排名、累计计算、滑动窗口。JOIN关联多表数据注意过滤条件前置。正则 / JSON 函数处理复杂字符串和嵌套结构。 性能优化 用 EXPLAIN 分析执行计划避免全表扫描。对大表 JOIN 考虑 MapJoin 或分桶表。过滤条件尽量前置减少中间数据量。
