requirement
- You need to enter dB by the given field_ Create their own mode under qingniu
- The tables created need to meet the technical specifications
- Multiple answer results
- The shorter the SQL execution time, the better
- You need to provide SQL statements in the process of creating table statements and inserting statements
Known table information
- Table structure
Table 1: exam_record, fields (uid user ID, exam_id, exam ID, start_time, start answering time, submit_time, submission time, score)
Table 2: examination_info, fields (exam_id, test ID, tag, test category, difficulty, duration, test duration, release_time)
- Table data
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'), (9002, 'SQL', 'easy', 60, '2020-02-01 10:00:00'), (9003, 'algorithm', 'medium', 80, '2020-08-02 10:00:00'), (9004, 'High number', 'medium', 80, '2020-08-02 10:00:00'); INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80), (1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81), (1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84), (1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89), (1002, 9002, '2021-09-02 12:01:01', null, null), (1002, 9003, '2021-09-01 12:01:01', null, null), (1002, 9001, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87), (1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90), (1003, 9002, '2021-02-06 12:01:01', null, null), (1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86), (1004, 9003, '2021-09-06 12:01:01', null, null), (1004, 9004, '2021-09-06 12:01:01', null, null);
Create table statement
Considering the type of test paper, the difficulty of test paper is generally a single value code, so VC100 is used
CREATE TABLE "db_bjj"."examination_info" ( "exam_id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL, "tag" varchar(100) COLLATE "pg_catalog"."default", "difficulty" varchar(100) COLLATE "pg_catalog"."default", "duration" varchar(300) COLLATE "pg_catalog"."default", "release_time" timestamp(6) ) ; ALTER TABLE "db_bjj"."examination_info" OWNER TO "sa"; COMMENT ON COLUMN "db_bjj"."examination_info"."exam_id" IS 'test paper ID'; COMMENT ON COLUMN "db_bjj"."examination_info"."tag" IS 'Test paper category'; COMMENT ON COLUMN "db_bjj"."examination_info"."difficulty" IS 'Test paper difficulty'; COMMENT ON COLUMN "db_bjj"."examination_info"."duration" IS 'Examination duration'; COMMENT ON COLUMN "db_bjj"."examination_info"."release_time" IS 'Release time'; COMMENT ON TABLE "db_bjj"."examination_info" IS 'Test paper information form'; CREATE TABLE "db_bjj"."exam_record" ( "uid" varchar(32) COLLATE "pg_catalog"."default" NOT NULL, "exam_id" varchar(32) COLLATE "pg_catalog"."default", "start_time" timestamp(6), "submit_time" timestamp(6), "score" int4 ) ; ALTER TABLE "db_bjj"."exam_record" OWNER TO "sa"; COMMENT ON COLUMN "db_bjj"."exam_record"."uid" IS 'user ID'; COMMENT ON COLUMN "db_bjj"."exam_record"."exam_id" IS 'test paper ID'; COMMENT ON COLUMN "db_bjj"."exam_record"."start_time" IS 'start time'; COMMENT ON COLUMN "db_bjj"."exam_record"."submit_time" IS 'Submission time'; COMMENT ON COLUMN "db_bjj"."exam_record"."score" IS 'score'; COMMENT ON TABLE "db_bjj"."exam_record" IS 'Test paper score table';
Topic 1: find the lowest score that is not less than the average score
- describe
The lowest score of users whose SQL test paper score is not less than the average score of this kind of test paper,
- example
For the SQL category of the test paper, the scores of the two test papers are [80,81,84,89,87,90], the average score is 85.16, and the minimum score not less than the average score is 87
Idea: 1. In table Association, the small table comes first and the large table comes last, so as to reduce the amount of data as much as possible. 2
2. Use the alias of the table to reduce the resolution time
3. According to the sql execution order, select after where. Filter out more data in the where condition as much as possible
Method 1
The most common way of writing is to find out the average value first, then obtain a score greater than the average value, and take the minimum score
SELECT MIN ( score ) FROM exam_record WHERE score > ( SELECT AVG ( score ) FROM "examination_info" info LEFT JOIN exam_record record ON record.exam_id = info.exam_id WHERE info.tag = 'SQL' ) AND exam_record.exam_id < '9003'
Method 2
Temporary tables that exist for queries using with
WITH bjj AS ( SELECT record.score FROM examination_info info LEFT JOIN exam_record record ON record.exam_id = info.exam_id WHERE record.score IS NOT NULL AND info.tag = 'SQL' ) SELECT MIN ( score ) FROM bjj WHERE score >= ( SELECT AVG ( score ) FROM bjj )
Method 3
Window function
WITH bjj AS ( SELECT record.score, AVG ( score ) OVER ( PARTITION BY tag ) AS AVG FROM exam_record record LEFT JOIN examination_info info ON info.exam_id = record.exam_id WHERE record.score IS NOT NULL AND info.tag = 'SQL' ) SELECT MIN ( score ) FROM bjj WHERE bjj.score > bjj.AVG
Topic 2: find the truncated average of scores
- describe
Truncated average value of score of SQL test paper with difficulty of hard (average value after removing a maximum value and a minimum value)
- example
The scores of the test paper are [80,81,84,89,87], excluding the highest and lowest scores, it is [87,81,84], with an average score of 84
Method 1
Processing with mathematical addition, subtraction, multiplication and division
SELECT ( SUM ( score ) - MAX ( score ) - MIN ( score ) ) / ( COUNT ( score ) - 2 ) AS AVG FROM examination_info info LEFT JOIN exam_record record ON record.exam_id = info.exam_id WHERE info.tag = 'SQL' AND info.difficulty = 'hard'
Method 2
Window function
With bjj as( SELECT record.score, RANK ( ) OVER ( PARTITION BY tag ORDER BY record.score ASC ) AS ASC, RANK ( ) OVER ( PARTITION BY tag ORDER BY record.score DESC ) AS DESC FROM exam_record record LEFT JOIN examination_info info ON info.exam_id = record.exam_id WHERE record.score IS NOT NULL AND info.tag = 'SQL' AND info.difficulty = 'hard' ) SELECT round(avg(score)) from bjj where bjj.ASC <> '1' and bjj.DESC <> '1'
Topic 3: count the number of answers
-
describe
Count the number of answers, the number of test papers completed and the number of test papers completed
-
example
Indicates that up to now, there are 12 test paper answering records, the number of completed answers is 7 (those who quit halfway are in the unfinished state, and the submission time and number of copies are NULL), and the number of completed test papers (900190029003, not in the unfinished state)
Output answer: 12|7|3
SELECT concat ( A.ALL, '|', A.ywczd, '|', A.ywccs ) FROM ( SELECT COUNT ( uid ) AS ALL, SUM ( CASE WHEN submit_time IS NOT NULL THEN 1 ELSE 0 END ) AS ywczd, COUNT ( DISTINCT CASE WHEN submit_time IS NOT NULL THEN exam_id ELSE NULL END ) AS ywccs FROM exam_record ) A wczd, '|', A.ywccs ) FROM ( SELECT COUNT ( uid ) AS ALL, SUM ( CASE WHEN submit_time IS NOT NULL THEN 1 ELSE 0 END ) AS ywczd, COUNT ( DISTINCT CASE WHEN submit_time IS NOT NULL THEN exam_id ELSE NULL END ) AS ywccs FROM exam_record ) A
------------------------------------- Create table:------------------------------------- CREATE TABLE "db_zyg"."exam_record" ( "c_bh" varchar(32) COLLATE "pg_catalog"."default" NOT NULL, "uid" varchar(32) COLLATE "pg_catalog"."default", "exam_id" varchar(32) COLLATE "pg_catalog"."default", "start_time" timestamp(6), "submit_time" timestamp(6), "score" int4, "c_cjr" varchar(300) COLLATE "pg_catalog"."default", "dt_cjsj" timestamp(6), "c_xgr" varchar(300) COLLATE "pg_catalog"."default", "dt_zhxgsj" timestamp(6), CONSTRAINT "exam_record_pkey" PRIMARY KEY ("c_bh") ) ; CREATE TABLE "db_zyg"."examination_info" ( "exam_id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL, "tag" varchar(300) COLLATE "pg_catalog"."default", "difficulty" varchar(300) COLLATE "pg_catalog"."default", "duration" varchar(300) COLLATE "pg_catalog"."default", "release_time" timestamp(6), "c_cjr" varchar(300) COLLATE "pg_catalog"."default", "dt_cjsj" timestamp(6), "c_xgr" varchar(300) COLLATE "pg_catalog"."default", "dt_zhxgsj" timestamp(6), CONSTRAINT "examination_info_pkey" PRIMARY KEY ("exam_id") ) ; ------------------------------------- Insert:------------------------------------- INSERT INTO "db_zyg"."exam_record" VALUES ('0', '1001', '9001', '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('1', '1001', '9001', '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('2', '1001', '9001', '2021-06-02 19:01:01', '2021-06-02 19:31:01', 84, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('3', '1002', '9001', '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('4', '1002', '9002', '2021-09-02 12:01:01', NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('5', '1002', '9003', '2021-09-01 12:01:01', NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('6', '1002', '9001', '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('7', '1002', '9002', '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('8', '1003', '9002', '2021-02-06 12:01:01', NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('9', '1003', '9003', '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('10', '1004', '9003', '2021-09-06 12:01:01', NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."exam_record" VALUES ('11', '1004', '9004', '2021-09-06 12:01:01', NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."examination_info" VALUES ('9001', 'SQL', 'hard', '60', '2020-01-01 10:00:00', NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."examination_info" VALUES ('9002', 'SQL', 'easy', '60', '2020-02-01 10:00:00', NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."examination_info" VALUES ('9003', 'algorithm', 'medium', '80', '2020-08-02 10:00:00', NULL, NULL, NULL, NULL); INSERT INTO "db_zyg"."examination_info" VALUES ('9004', 'High number', 'medium', '80', '2020-08-02 10:00:00', NULL, NULL, NULL, NULL); ------------------------------------- Topic 1:------------------------------------- SELECT score FROM db_zyg.exam_record re INNER JOIN examination_info info ON re.exam_id = info.exam_id WHERE tag = 'SQL' AND score > ( SELECT AVG ( score ) FROM db_zyg.exam_record re INNER JOIN examination_info info ON re.exam_id = info.exam_id WHERE tag = 'SQL' AND score IS NOT NULL ) ORDER BY score ASC LIMIT 1; ------------------------------------- Topic 2:------------------------------------- SELECT AVG ( score ) FROM ( SELECT score, ROW_NUMBER ( ) OVER ( ORDER BY score ) AS ROW_NUMBER FROM db_zyg.exam_record re INNER JOIN examination_info info ON re.exam_id = info.exam_id WHERE tag = 'SQL' AND difficulty = 'hard' AND score IS NOT NULL ) TEMP WHERE ROW_NUMBER != 1 AND ROW_NUMBER != ( SELECT COUNT ( 1 ) FROM db_zyg.exam_record re INNER JOIN examination_info info ON re.exam_id = info.exam_id WHERE tag = 'SQL' AND difficulty = 'hard' AND score IS NOT NULL ); SELECT AVG ( score ) FROM ( SELECT score FROM db_zyg.exam_record re INNER JOIN examination_info info ON re.exam_id = info.exam_id WHERE tag = 'SQL' AND difficulty = 'hard' AND score IS NOT NULL ORDER BY score LIMIT ( SELECT COUNT ( 1 ) - 2 FROM db_zyg.exam_record re INNER JOIN examination_info info ON re.exam_id = info.exam_id WHERE tag = 'SQL' AND difficulty = 'hard' AND score IS NOT NULL ) OFFSET 1 ) TEMP; SELECT AVG ( score ) FROM ( SELECT score FROM db_zyg.exam_record re INNER JOIN examination_info info ON re.exam_id = info.exam_id WHERE tag = 'SQL' AND difficulty = 'hard' AND c_bh NOT IN ( ( SELECT c_bh FROM db_zyg.exam_record re INNER JOIN examination_info info ON re.exam_id = info.exam_id WHERE tag = 'SQL' AND difficulty = 'hard' ORDER BY score ASC LIMIT 1 ) UNION ALL ( SELECT c_bh FROM db_zyg.exam_record re INNER JOIN examination_info info ON re.exam_id = info.exam_id WHERE tag = 'SQL' AND difficulty = 'hard' ORDER BY score DESC LIMIT 1 ) ) ) TEMP; ------------------------------------- Topic 3:------------------------------------- SELECT * FROM ( SELECT COUNT ( 1 ), SUM ( CASE WHEN score IS NULL THEN 0 ELSE 1 END ) FROM db_zyg.exam_record ) t1, ( SELECT COUNT ( DISTINCT exam_id ) FROM db_zyg.exam_record WHERE score IS NOT NULL ) t2; SELECT * FROM ( SELECT COUNT ( 1 ), SUM ( CASE WHEN score IS NULL THEN 0 ELSE 1 END ) FROM db_zyg.exam_record ) t1, ( SELECT COUNT ( 1 ) FROM ( SELECT exam_id FROM db_zyg.exam_record WHERE score IS NOT NULL GROUP BY exam_id ) T ) t2;