postgresql 如何根据已发生和未发生的事件进行筛选

g6ll5ycj 于 2022-11-23 发布在 PostgreSQL

关注(0)|答案(2)|浏览(159)

我在SQL数据库中有一个名为orders的表，如下所示：

user_id    email            segment    destination    revenue    
    1          joe@smith.com    basic      New York       500
    1          joe@smith.com    luxury     London         750
    1          joe@smith.com    luxury     London         500
    1          joe@smith.com    basic      New York       625
    1          joe@smith.com    basic      Miami          925
    1          joe@smith.com    basic      Los Angeles    218
    1          joe@smith.com    basic      Sydney         200
    2          mary@jones.com   basic      Chicago        375
    2          mary@jones.com   luxury     New York       1500
    2          mary@jones.com   basic      Toronto        2800
    2          mary@jones.com   basic      Miami          750
    2          mary@jones.com   basic      New York       500
    2          mary@jones.com   basic      New York       625
    3          mike@me.com      luxury     New York       650
    3          mike@me.com      basic      New York       875
    4          sally@you.com    luxury     Chicago        1300
    4          sally@you.com    basic      New York       1200
    4          sally@you.com    basic      New York       1000
    4          sally@you.com    luxury     Sydney         725
    5          bob@gmail.com    basic      London         500
    5          bob@gmail.com    luxury     London         750

下面是一个SQL Fiddle：http://www.sqlfiddle.com/#!9/22f40a/1
我希望能够应用以下逻辑来获得最终结果集：
根据下列条件，只传回不同的user_id和使用者的email：
1.其中segment等于luxury***，***destination等于纽约

或

1.其中segment等于luxury***，并且***destination等于London

或

1.其中segment等于basic***，***destination等于New York***，***给定用户在basic和New York记录中具有revenue金额，该金额的总和大于$2，000
我的天啊
1.给定用户***以前***没有去过等于Miami的destination
根据我的示例数据，我希望返回以下内容：

user_id     email
3           mike@me.com
4           sally@you.com
5           bob@gmail.com

我尝试使用以下内容来获得我所需的***部分***：

SELECT
   DISTINCT(user_id),
   email
FROM orders o

WHERE
(o.segment = 'luxury' AND o.destination = 'New York')
OR
(o.segment = 'luxury' AND o.destination = 'London')

但是，这个查询不能处理上面的条件#3和#4。我觉得窗口函数可能会有帮助，但是我不知道如何实现它。
如果有人能帮我解决这个问题，我将非常感激！
谢谢你！

postgresql

来源：https://stackoverflow.com/questions/74527021/how-to-filter-based-on-events-that-have-happened-and-have-not-happened

2条答案

按热度按时间

cygmwpex1#

您可以使用子查询来达成您的需求：

SELECT
   DISTINCT(o.user_id),
   o.email
FROM orders o
WHERE
  (
    -- Clause 1
    (o.segment = 'luxury' AND o.destination = 'New York')
    OR
    -- Clause 2
    (o.segment = 'luxury' AND o.destination = 'London')
    OR
    -- Clause 3
    (o.user_id IN (
      SELECT DISTINCT(o.user_id)
      FROM orders o
      WHERE o.segment = 'basic' AND o.destination = 'New York'
      GROUP BY o.user_id, o.email, o.segment, o.destination
      HAVING SUM(o.revenue) > 2000
    ))
  )
  AND
  -- Clause 4
  o.user_id NOT IN (
    SELECT DISTINCT(o.user_id)
    FROM orders o
    WHERE o.destination = 'Miami'
  )

赞(0）回复(0）举报 2022-11-23

zc0qhyus2#

下面是另一种方法，只需扫描表一次，即group by和having：

SELECT user_id, email,
       SUM(case
          when segment='luxury' and destination in ('New York','London') then 1 
          else 0 
       end) as is_luxury,
       SUM(case
          when segment='basic' and destination in ('New York') then 1
          else 0
       end) as is_basic,       
       SUM(case
          when segment='basic' and destination in ('New York') then revenue
          else 0
       end) as basic_revenue,
       SUM(case when destination in ('Miami') then 1 else 0 end) as is_miami
FROM orders
GROUP BY 1,2
HAVING (is_luxury > 0 OR (is_basic > 0 AND basic_revenue > 2000))
  AND NOT is_miami;

赞(0）回复(0）举报 2022-11-23

我来回答

postgresql 如何根据已发生和未发生的事件进行筛选

2条答案

相关问题

热门标签

最新问答