SQL如何在满足条件时跳过最大行

Question

我需要根据条件返回最新可用的行。 由于Hive不支持PL-T/SQL，因此我需要使用函数。

当前代码仅选择最新记录，没有考虑ACTIVE_F。

WITH CTE AS 

(select 
ID, 
myuser_insert_time as insert_time,
max(myuser_insert_time) OVER (PARTITION BY ID ORDER BY ID) as rn
from tbl1)

SELECT * FROM CTE 
WHERE rn =  insert_time

我的数据：

MYUSER_INSERT_TIME        ACTIVE_F
2019-06-14 15:00:32.000   6
2019-03-06 15:54:22.000   0
2019-01-25 08:43:45.000   1
2018-12-13 09:49:50.000   0
2018-11-24 10:11:06.000   0
2018-11-06 12:17:34.000   1
2018-07-04 16:59:15.000   0
2018-05-29 12:22:15.000   1
2018-05-24 20:19:00.000   2
2018-05-24 20:19:00.000   2

期望的行为：

1. 查找最新记录（已完成）

2. 检查ACTIVE_F（当为6时-移动到下一行并返回该行，否则继续到下一行）

所需结果：

MYUSER_INSERT_TIME        ACTIVE_F
2019-03-06 15:54:22.000   0

- marcin2x4

---

...从tbl1表中选择ACTIVE_F不等于6的记录。 - Serg

你的表中有没有任何列可以用于“order by”子句？ - notNull

@Serg - 我不能简单地删除 6，因为它可能是某些情况下唯一的记录。 - marcin2x4

@Shu - 是的，MYUSER_INSERT_TIME。 - marcin2x4

Answer 1

按照ACTIVE_F条件有序排列行，这样6将排在所有其他值之后

WITH CTE AS 

(select 
ID, 
myuser_insert_time as insert_time,
row_number() OVER (PARTITION BY ID ORDER BY case ACTIVE_F when 6 then 1 else 0 end,  eendmyuser_insert_time desc) as rn
from tbl1)

SELECT * FROM CTE 
WHERE rn = 1

- Serg

---

我想我们搞定了！我会在样本结果上进行测试并让你知道！ - marcin2x4

这个完美运作！我在 ORDER BY 中扩展了 CASE，以容纳一个需要在第二个位置迭代的标志。ORDER BY case ACTIVE_F when 6 then 1 when 0 then 1 else 0 end, eendmyuser_insert_time desc - marcin2x4

Answer 2

如果我理解正确，您似乎只想过滤掉active_f = 6。您应该在计算最大插入时间之前，在CTE中进行此操作。

with cte as (
      select ID, myuser_insert_time as insert_time,
             max(myuser_insert_time) over (partition by ID) as max_myuser_insert_time
      from tbl1
      where active_f <> 6
    )
select * 
from CTE 
where myuser_insert_time = insert_time;

你的max()函数还有一个order by列，所以你在进行累积max()。这是不必要的。即使代码能够运行，order by也是不必要的。

编辑：

如果只有一行需要“6”，那么使用row_number()将其作为最后一行。

with cte as (
      select ID, myuser_insert_time as insert_time,
             row_number() over (partition by id
                                order by (case when active_f = 6 then 2 else 1 end),
                                         myuser_insert_time desc
                               ) as rn
      from tbl1
    )
select * 
from CTE 
where rn = 1;

- Gordon Linoff

---

很遗憾，在这里消除 6 并不是一个解决方法。可能存在只有一行存在 - 其中包含 6 的情况。如果他是唯一的，则应该显示他。 - marcin2x4

@marcin2x4……我认为问题中并不清楚。 - Gordon Linoff

我再强调一遍。我需要找到在 ACTIVE_F = 6 行之后出现的第一行最新记录。如果第二行也是 6，则继续循环。 - marcin2x4

@marcin2x4... 这就是编辑后的代码所做的。 - Gordon Linoff

按照以下顺序排序(case when active_f = 6 then 2 else 1 end)，myuser_insert_time DESC - DESC修复了问题，因为我需要最新的(插入时间)记录 :) - marcin2x4

Answer 3

在分析函数的order by子句中添加case when active_f =6 then 1 else 0 end。将优先选择active_f !=6的记录。同时使用row_number和myuser_insert_time desc进行排序。

WITH CTE AS 

(select 
ID, 
myuser_insert_time as insert_time,
row_number() OVER (PARTITION BY ID ORDER BY myuser_insert_time desc, case when active_f =6 then 1 else 0 end) as rn
from tbl1)

SELECT * FROM CTE 

WHERE rn =  1

- leftjoin

---

快了，我需要倒数第二行。 - marcin2x4