如何根据具体情况选择记录

Question

我有一个名为"ticket_lc“的表，如下所示

​

​

从上面的表格中，我只需要挑选满足条件的记录

条件:工单状态应为已分配、已关闭、已解决

因此，在上表中，只有102个票证满足条件，如果票证包含这3个以外的其他票证，则我的查询不应选择这些票证。

有人能帮我吗..！

谢谢

Answer 1

下面是针对BigQuery标准SQL的说明

#standardSQL
SELECT ticket_id
FROM `project.dataset.ticket_lc`
GROUP BY ticket_id
HAVING COUNT(DISTINCT status) = 3
AND COUNTIF(LOWER(status) NOT IN ('assigned', 'closed', 'resolved')) = 0 

你可以使用你的问题中的样本数据来测试，玩上面的游戏，如下面的例子

#standardSQL
WITH `project.dataset.ticket_lc` AS (
  SELECT 101 ticket_id, 'Assigned' status UNION ALL
  SELECT 101, 'Pending' UNION ALL
  SELECT 101, 'Resolved' UNION ALL
  SELECT 101, 'Closed' UNION ALL
  SELECT 102, 'Assigned' UNION ALL
  SELECT 102, 'Resolved' UNION ALL
  SELECT 102, 'Closed' UNION ALL
  SELECT 103, 'Assigned' UNION ALL
  SELECT 103, 'Pending' UNION ALL
  SELECT 103, 'Pending' UNION ALL
  SELECT 103, 'Assigned' UNION ALL
  SELECT 103, 'Resolved' UNION ALL
  SELECT 103, 'Closed' 
)
SELECT ticket_id
FROM `project.dataset.ticket_lc`
GROUP BY ticket_id
HAVING COUNT(DISTINCT status) = 3
AND COUNTIF( LOWER(status) NOT IN ('assigned', 'closed', 'resolved')) = 0 

有结果

Row ticket_id    
1   102  

Answer 2

您可以进行聚合：

select ticket_id
from table t
group by ticket_id
having sum( case when status not in ('assigned', 'closed', 'resolved') then 1 else 0 end ) = 0 and
       count(*) = 3;

如果票证的状态重复，请在count()中使用distinct。

Answer 3

这是个有趣的问题。我想出了这个检查，如果一切都涵盖了，金额是正确的：

WITH t AS (SELECT * FROM UNNEST(
  [struct(101 as ticket_id, 'assigned' as status),(101,'closed'),
    (102,'assigned'),(102,'resolved'),(102,'closed'),
    (104,'assigned'),(104,'assigned'),(104,'closed'),
    (103,'assigned'),(103,'pending'),(103,'pending'),(103,'assigned'),(103,'resolved'),(103,'closed')]  
  )
)

SELECT ticket_id, array_agg(distinct status) as st 
FROM t
group by 1
having (SELECT count(1)=3 FROM unnest(st) a left join unnest(['assigned','resolved','closed']) b on a=b)

包括调整后的样本数据，以涵盖更多问题案例。