【详述】使用SQL获取某个月最大和最小日期的当前行的数据(按照用户和月份进行分组)，并使用窗口函数将它们组合成一行记录，已通过mysql的first_value和last_value函数加子查询实现了它，但到了StarRocks这边、因为语法是强制查询字段必须出现在分组里或使用聚合函数，first_value和last_value并不需要再次进行分组，只需要取出结果。
【背景】
示例
| uid | date | score |
|---|---|---|
| 1001 | 2022-11-01 | 50 |
| 1001 | 2022-11-15 | 100 |
| 1001 | 2022-11-27 | 20 |
预期
| uid | date | first_score | last_score |
|---|---|---|---|
| 1001 | 2022-11 | 50 | 20 |
建表语句
CREATE TABLE `exam_record` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`uid` int(0) NOT NULL COMMENT '用户ID',
`exam_id` int(0) NOT NULL COMMENT '试卷ID',
`start_time` datetime(0) NOT NULL COMMENT '开始时间',
`submit_time` datetime(0) NULL DEFAULT NULL COMMENT '提交时间',
`score` tinyint(0) NULL DEFAULT NULL COMMENT '得分',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of exam_record
-- ----------------------------
INSERT INTO `exam_record` VALUES (1, 1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90);
INSERT INTO `exam_record` VALUES (2, 1002, 9001, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 89);
INSERT INTO `exam_record` VALUES (3, 1002, 9001, '2020-02-01 12:11:01', '2020-02-01 12:31:01', 83);
INSERT INTO `exam_record` VALUES (4, 1003, 9001, '2020-03-01 19:01:01', '2020-03-01 19:30:01', 75);
INSERT INTO `exam_record` VALUES (5, 1004, 9001, '2020-03-01 12:01:01', '2020-03-01 12:11:01', 60);
INSERT INTO `exam_record` VALUES (6, 1003, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90);
INSERT INTO `exam_record` VALUES (7, 1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90);
INSERT INTO `exam_record` VALUES (8, 1001, 9002, '2020-03-18 19:01:01', '2020-01-02 19:59:01', 69);
INSERT INTO `exam_record` VALUES (9, 1004, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99);
INSERT INTO `exam_record` VALUES (10, 1003, 9002, '2020-02-02 12:01:01', '2020-02-02 12:31:01', 68);
INSERT INTO `exam_record` VALUES (11, 1001, 9002, '2020-03-11 12:01:01', '2020-02-02 12:43:01', 100);
INSERT INTO `exam_record` VALUES (12, 1001, 9002, '2020-03-02 12:11:01', '2022-11-21 20:20:25', 80);
INSERT INTO `exam_record` VALUES (13, 1001, 9002, '2020-03-27 20:44:24', '2022-11-21 20:44:36', 10);
MySQL实现
SELECT
uid,
start_time,
first_score,
last_score
FROM
(
SELECT
uid,
DATE_FORMAT( start_time, '%Y-%m-%d' ) AS start_time,
first_value( score ) over ( PARTITION BY uid, DATE_FORMAT( start_time, '%Y-%m' ) ORDER BY start_time DESC ) first_score,
last_value( score ) over ( PARTITION BY uid, DATE_FORMAT( start_time, '%Y-%m' ) ORDER BY start_time DESC ) last_score
FROM exam_record
GROUP BY uid, DATE_FORMAT( start_time, '%Y-%m-%d' )
ORDER BY uid, DATE_FORMAT( start_time, '%Y-%m-%d' ) ASC
) AS temp_table
GROUP BY uid, DATE_FORMAT( start_time, '%Y-%m')
ORDER BY uid, start_time
【业务影响】无
【StarRocks版本】2.1.8 0e4b1da