时间戳

首页 > 解决方案 > SQL - 在表中选择迄今为止最接近的值

sql - SQL - 在表中选择迄今为止最接近的值

问题描述

大家好，我有一张叫做 measure 的表，是这样组成的

id                                  |date               |value|type
05f643f4-9df9-4b29-b347-7e7627a12568|2020-03-15 22:00:00| 87.5|WEIGHT
3d2489cc-1c3b-40c4-8163-dd5d89281ce8|2020-04-20 22:00:00| 51.6|WEIGHT
610b3de3-ad28-4fc1-8f28-595e6464f58a|2020-04-19 22:00:00| 80  |ONERM_DEADWEIGHT
610b3de3-ad28-4fc1-8f28-595e6464f58c|2020-04-05 22:00:00| 79  |ONERM_SQUAT
610b3de3-ad28-4fc1-8f28-595e6464f58d|2020-04-01 22:00:00| 78.5|ONERM_BENCHPRESS
610b3de3-ad28-4fc1-8f28-595e6464f58e|2020-04-15 22:00:00| 81  |ONERM_DEADWEIGHT
6147803f-ee10-499e-9990-814d9562527a|2020-04-16 22:00:00| 77.2|WEIGHT
6ca210c3-0667-40e9-9d1b-d8bae3e43d9b|2020-04-19 22:00:00| 106 |ONERM_BENCHPRESS
76dd309d-b80d-4bad-b6a6-bf2b0d62adb9|2020-02-01 22:00:00| 120 |ONERM_SQUAT
774e74ac-40da-4232-be10-98ca56050d52|2020-03-01 22:00:00| 106 |ONERM_BENCHPRESS

我想选择所有最低值，例如

id                                  |date               |value|type
05f643f4-9df9-4b29-b347-7e7627a12568|2020-03-15 22:00:00| 87.5|WEIGHT
610b3de3-ad28-4fc1-8f28-595e6464f58e|2020-04-15 22:00:00| 81  |ONERM_DEADWEIGHT
76dd309d-b80d-4bad-b6a6-bf2b0d62adb9|2020-02-01 22:00:00| 120 |ONERM_SQUAT
774e74ac-40da-4232-be10-98ca56050d52|2020-03-01 22:00:00| 106 |ONERM_BENCHPRESS

我可以通过代码来完成，但出于性能原因我更喜欢使用 SQL，所以我尝试了这个查询：

SELECT m.value AS value, m.type AS type, 
MIN(m.date) AS date 
FROM measure m 
GROUP BY m.date, m.type, m.value

但是结果并不好，我看到的结果太多了，而且还莫名其妙地重复了，我该怎么办？

编辑非常感谢您的帮助，最后的丑陋查询是


SELECT `startValues`.`type`, `startValues`.`value` AS `start`, `startValues`.`date` AS `startDate`, `endValues`.`value` AS `end`, `endValues`.`date` AS `endDate`
FROM (
    SELECT cte.* FROM 
    (SELECT *, row_number()  OVER(PARTITION BY `type` ORDER BY `date`) AS rwn FROM measure AS rwn 
                WHERE `createdById`='076e0e51-cd29-4451-bf99-f145c4498c1c' 
                AND `date` >= '2020-01-01 00:00:00.000'
                AND `date` <= '2020-05-01 00:00:00.000'
    )  AS cte
    WHERE cte.rwn = 1
) AS startValues
JOIN (
    SELECT cte.* FROM 
    (SELECT *, row_number()  OVER(PARTITION BY `type` ORDER BY `date` DESC) AS rwn FROM measure AS rwn 
                WHERE `createdById`='076e0e51-cd29-4451-bf99-f145c4498c1c' 
                AND `date` >= '2020-01-01 00:00:00.000'
                AND `date` <= '2020-05-01 00:00:00.000'
    )  AS cte
    WHERE cte.rwn = 1
) AS endValues ON `startValues`.`type` = `endValues`.`type`

这样我就可以有一张这样的桌子

type             |start|startDate          |end  |endDate
WEIGHT           |87.5 |2020-02-15 22:00:00| 77.2|2020-04-19 22:00:00
ONERM_DEADWEIGHT |78.5 |2020-04-01 22:00:00| 80  |2020-04-19 22:00:00
ONERM_SQUAT      |55   |2020-04-01 22:00:00| 60  |2020-04-19 22:00:00
ONERM_BENCHPRESS |67   |2020-04-01 22:00:00| 75  |2020-04-19 22:00:00

如果您想随时优化我的查询

标签： sqldatetimemariadbgroupwise-maximum

解决方案

您可以将该ROW_NUMBER函数用作带有 的窗口函数OVER。分区中的行数和排序依据date

with cte as(
select * , row_number() over (partition by type order by date) as rwn
  from measure)

select *
  from cte 
 where rwn = 1

推荐阅读