SQL - 重用具有不同过滤条件的窗口函数分区

发布于 2025-01-15 10:35:36 字数 617 浏览 2 评论 0原文

我有一个包含以下字段的表 orders：

order_id: varchar
user_id:  varchar
item_id:  varchar
datetime: timestamp
quantity: int

以下查询将帮助我找到每个 user_id 的最后一个项目，其中数量 > 10.

WITH ranked AS (
    SELECT *,
        row_number() OVER (PARTITION BY user_id ORDER BY datetime DESC)
    FROM orders
    WHERE quantity > 10
)
SELECT user_id, item_id, datetime FROM ranked where rn = 1

现在，不再考虑数量>的订单。 10，我想找到相同的信息，但具有不同的阈值，例如 20、50、100。

一种选择是另外 3 个不同的查询，每个阈值一个。然而，它看起来效率不高。有更好的查询吗？

谢谢。

P/S：我需要一个用于 SparkSQL 或 Postgres 的。不过，通用 SQL 就可以了。

原文

I have a table orders with the following fields:

order_id: varchar
user_id:  varchar
item_id:  varchar
datetime: timestamp
quantity: int

The following query would help me find the last item by each user_id where the quantity is > 10.

WITH ranked AS (
    SELECT *,
        row_number() OVER (PARTITION BY user_id ORDER BY datetime DESC)
    FROM orders
    WHERE quantity > 10
)
SELECT user_id, item_id, datetime FROM ranked where rn = 1

Now, instead of considering orders with quantity > 10, I want to find the same information but with different thresholds, like 20, 50, 100.

One option is to have another 3 different queries, one for each threshold. However, it looks not efficient. Is there a better query?

Thanks.

P/S: I will need one for SparkSQL or Postgres. However, generic SQL is fine.

分享到QQ

分享到微博