You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
|
|
|
|
|
/*
|
|
|
|
|
|
查询:
|
|
|
|
|
|
1、发布时间是2024年度
|
|
|
|
|
|
2、每个学段，每个科目，上传课程数量，按由多到少排序
|
|
|
|
|
|
3、字段名: 学段,科目,排名,课程数量
|
|
|
|
|
|
*/
|
|
|
|
|
|
SELECT stage_name AS 学段, subject_name AS 科目, ROW_NUMBER() OVER (PARTITION BY stage_name ORDER BY COUNT(*) DESC) AS 排名, COUNT(*) AS 课程数量 FROM t_crawler_lesson WHERE publish_time >= '2024-01-01' AND publish_time < '2025-01-01' AND gather_regionc IS NOT NULL AND gather_regionc <> '' GROUP BY stage_name, subject_name ORDER BY stage_name, 课程数量 DESC;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/*
|
|
|
|
|
|
查询:
|
|
|
|
|
|
1、发布时间是2024年度
|
|
|
|
|
|
2、每个行政区每个学校都上传了多少课程数量
|
|
|
|
|
|
3、格式: 行政区划名,学段,排名,学校名称,课程数量
|
|
|
|
|
|
*/
|
|
|
|
|
|
SELECT gather_regionc AS 行政区划名, stage_name AS 学段, ROW_NUMBER() OVER (PARTITION BY gather_regionc ORDER BY COUNT(*) DESC) AS 排名, teacher_school_name AS 学校名称, COUNT(*) AS 课程数量 FROM t_crawler_lesson WHERE publish_time >= '2024-01-01' AND publish_time < '2025-01-01' AND gather_regionc IS NOT NULL AND gather_regionc != '' GROUP BY gather_regionc, stage_name, teacher_school_name ORDER BY gather_regionc, COUNT(*) DESC;
|
|
|
|
|
|
|
|
|
|
|
|
/*
|
|
|
|
|
|
查询:
|
|
|
|
|
|
1、发布时间是2024年度
|
|
|
|
|
|
2、二道区每个学校都上传了多少课程数量
|
|
|
|
|
|
3、格式: 行政区划名,学段,排名,学校名称,发布年份,课程数量
|
|
|
|
|
|
*/
|
|
|
|
|
|
SELECT gather_regionc AS 行政区划名, stage_name AS 学段, ROW_NUMBER() OVER (PARTITION BY gather_regionc ORDER BY COUNT(*) DESC) AS 排名, teacher_school_name AS 学校名称, EXTRACT(YEAR FROM publish_time) AS 发布年份, COUNT(*) AS 课程数量 FROM t_crawler_lesson WHERE publish_time >= '2024-01-01' AND publish_time < '2025-01-01' AND gather_regionc = '二道区' AND gather_regionc IS NOT NULL AND gather_regionc != '' GROUP BY gather_regionc, stage_name, teacher_school_name, EXTRACT(YEAR FROM publish_time) ORDER BY gather_regionc, 课程数量 DESC;
|
