SQL使用N-1 Dense Rank过滤掉密集秩N中的行 Mysql

2022-01-15 00:00:00 sql window-functions mariadb mysql

如果我的标题不清楚，这里是我所说的一个例子.

If my title isn't clear here's an example of what I am talking about.

假设我有一张如下所示的表格:

Let's say I have a table that looks like this:

+----+------+----+----+---------+-------+---------+-------+
| ID | Time | X  | Y  | X_START | X_END | Y_START | Y_END |
+----+------+----+----+---------+-------+---------+-------+
|  1 |    0 |  8 |  6 |       6 |    10 |       4 |     8 |
|  2 |    0 | 20 | 10 |      18 |    22 |       8 |    12 |
|  3 |    1 |  8 |  8 |       6 |    10 |       6 |    10 |
|  4 |    1 | 10 | 24 |       8 |    12 |      22 |    26 |
+----+------+----+----+---------+-------+---------+-------+

如果我将此查询应用于表:

If I apply this query to the table:

WITH 
cte1 AS (SELECT *,
    DENSE_RANK() OVER (ORDER BY TIME) AS DENSE_RANK
    FROM data
    ORDER BY TIME)

SELECT * FROM cte1

我明白了:

+----+------+----+----+---------+-------+---------+-------+------------+
| ID | Time | X  | Y  | X_START | X_END | Y_START | Y_END | DENSE_RANK |
+----+------+----+----+---------+-------+---------+-------+------------+
|  1 |    0 |  8 |  6 |       6 |    10 |       4 |     8 |          1 |
|  2 |    0 | 20 | 10 |      18 |    22 |       8 |    12 |          1 |
|  3 |    1 |  8 |  8 |       6 |    10 |       6 |    10 |          2 |
|  4 |    1 | 10 | 24 |       8 |    12 |      22 |    26 |          2 |
+----+------+----+----+---------+-------+---------+-------+------------+

现在我要做的是过滤掉 X 在 X_START - X_END 范围内且 Y 在 Y_START - Y_END 范围内且 DENSE_RANK 为 n-1 的任何行

Now what I want to do is filter out any rows where X is within the range X_START - X_END AND Y is within the range Y_START - Y_END AND DENSE_RANK is n-1

所以我想要一个看起来像这样的结果:

so I'd like a result that looks like this:

+----+------+----+----+---------+-------+---------+-------+------------+
| ID | Time | X  | Y  | X_START | X_END | Y_START | Y_END | DENSE_RANK |
+----+------+----+----+---------+-------+---------+-------+------------+
|  1 |    0 |  8 |  6 |       6 |    10 |       4 |     8 |          1 |
|  2 |    0 | 20 | 10 |      18 |    22 |       8 |    12 |          1 |
|  4 |    1 | 10 | 24 |       8 |    12 |      22 |    26 |          2 |
+----+------+----+----+---------+-------+---------+-------+------------+

我对 SQL 很陌生，所以我不太确定如何去做.提前感谢您的所有帮助！

I am pretty new to SQL so I am not too sure on how to go about this. Thank you for all help in advance!

推荐答案

使用MIN()窗口函数来识别每个Time的最小ID代码> 以便在所有其他条件也满足的情况下排除该行:

Use MIN() window function to identify the minimum ID for each Time so that you can exclude that row if all the other conditions are satisfied too:

WITH cte AS (
    SELECT *, 
           DENSE_RANK() OVER (ORDER BY TIME) AS `DENSE_RANK`,
           MIN(ID) OVER (PARTITION BY TIME) min_id
    FROM data
  )
SELECT ID, Time, X, Y, X_START, X_END, Y_START, Y_END, `DENSE_RANK` 
FROM cte
WHERE `DENSE_RANK` = 1 
  OR NOT (ID = min_id AND X BETWEEN X_START AND X_END AND Y BETWEEN Y_START AND Y_END)
ORDER BY `DENSE_RANK`, ID;

请参阅演示.

相关文章