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.
This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.
/*
查询:
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 ;
