Google标准SQL -已搜索目的地但未预订的id计数

Question

我在航空应用程序中有一张搜索数据表。我想写一个查询，输出只在纽约搜索一个机场而不是在纽约搜索任何其他机场的游客人数。所以从根本上计算出搜索一个机场而不去纽约其他机场的人的数量。

假设纽约机场是JFK、LGA、EWR和MSY。

表的外观大致如下：

+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| visitor_id | searched_to | searched_from | booked|
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
|     11     |     JFK     |      LCY      |   N   |
|     11     |     LGA     |      LCY      |   N   |
|     11     |     EWR     |      LCY      |   N   |
|     12     |     JFK     |      LCY      |   N   |
|     13     |     MSY     |      LTN      |   Y   |
|     14     |     JFK     |      LTN      |   N   |
|     14     |     JFK     |      LGW      |   N   |
|     14     |     EWR     |      LCY      |   Y   |
|     15     |     GOA     |      MSY      |   N   |

例如，访问者12和15将是一个人，我想包括在一个计数的那些没有扩大他们的搜索。例如，游客14将是一个人，我想包括在一个计数的那些确实延长了他们的搜索。

从本质上说，我想要的是：

• 没有扩展搜索，也没有预订
• 没有扩展搜索，也没有预订
• 确实扩展了搜索和书籍

希望这一切都有意义？任何帮助都将不胜感激！

Answer 1

下面是用于BigQuery标准SQL的

第一步是找出是谁做了什么。

#standardSQL
SELECT visitor_id, 'Y' = MAX(booked) booked,
  1 < COUNTIF(searched_to IN ('JFK', 'LGA', 'EWR', 'MSY') 
      OR searched_from IN ('JFK', 'LGA', 'EWR', 'MSY')) extended
FROM `project.dataset.your_table`
GROUP BY visitor_id  

如果您将使用虚拟数据进行尝试，如下所示

WITH `project.dataset.your_table` AS (
  SELECT 11 visitor_id, 'JFK' searched_to, 'LCY' searched_from, 'N' booked UNION ALL
  SELECT 11, 'LGA', 'LCY', 'N' UNION ALL
  SELECT 11, 'EWR', 'LCY', 'N' UNION ALL
  SELECT 12, 'JFK', 'LCY', 'N' UNION ALL
  SELECT 13, 'MSY', 'LTN', 'Y' UNION ALL
  SELECT 14, 'JFK', 'LTN', 'N' UNION ALL
  SELECT 14, 'JFK', 'LGW', 'N' UNION ALL
  SELECT 14, 'EWR', 'LCY', 'Y' UNION ALL
  SELECT 15, 'GOA', 'MSY', 'N' 
)

结果将是

Row visitor_id  booked  extended     
1   11          false   true     
2   12          false   false    
3   15          false   false    
4   13          true    false    
5   14          true    true     

下一步是做最后计数

#standardSQL
SELECT extended, booked, COUNT(1) cnt
FROM (
  SELECT visitor_id, 'Y' = MAX(booked) booked,
    1 < COUNTIF(searched_to IN ('JFK', 'LGA', 'EWR', 'MSY') 
        OR searched_from IN ('JFK', 'LGA', 'EWR', 'MSY')) extended
  FROM `project.dataset.your_table`
  GROUP BY visitor_id
)
GROUP BY extended, booked    

这个会回来的

Row extended    booked  cnt  
1   false       false   2    
2   false       true    1    
3   true        true    1    
4   true        false   1     

您可以使用问题中的虚拟数据来测试、处理整个查询。

#standardSQL
WITH `project.dataset.your_table` AS (
  SELECT 11 visitor_id, 'JFK' searched_to, 'LCY' searched_from, 'N' booked UNION ALL
  SELECT 11, 'LGA', 'LCY', 'N' UNION ALL
  SELECT 11, 'EWR', 'LCY', 'N' UNION ALL
  SELECT 12, 'JFK', 'LCY', 'N' UNION ALL
  SELECT 13, 'MSY', 'LTN', 'Y' UNION ALL
  SELECT 14, 'JFK', 'LTN', 'N' UNION ALL
  SELECT 14, 'JFK', 'LGW', 'N' UNION ALL
  SELECT 14, 'EWR', 'LCY', 'Y' UNION ALL
  SELECT 15, 'GOA', 'MSY', 'N' 
)
SELECT extended, booked, COUNT(1) cnt
FROM (
  SELECT visitor_id, 'Y' = MAX(booked) booked,
    1 < COUNTIF(searched_to IN ('JFK', 'LGA', 'EWR', 'MSY') 
        OR searched_from IN ('JFK', 'LGA', 'EWR', 'MSY')) extended
  FROM `project.dataset.your_table`
  GROUP BY visitor_id
)
GROUP BY extended, booked    

注意:在问题的标题中，您提到了searched for destination，但在问题本身中，您提到了要包含的访问者#15 --所以我在过滤中同时使用了searched_to和searched_from。如果需要，可以删除search_from的筛选器

Answer 2

为第一次查询尝试以下操作。

请检查此查询是否提供了所需的输出。

SELECT visitor_id, count(*)
FROM tableName t
WHERE search_to IN ('JFK', 'LGA', 'EWR', 'MSY')
AND NOT IN (SELECT visitor_id FROM tableName temp WHERE t.visitor_id = temp.visitor_id AND booked = 'Y' and search_to IN ('JFK', 'LGA', 'EWR', 'MSY'))
GROUP BY visitor_id
HAVING count(*) = 1;

经过一些修改，第二次和第三次查询就可以使用了。你自己试试吧。