如何使用BigQuery获得唯一对? [英] How can I get unique pairs using BigQuery?

问题描述

我有一个BigQuery表.我正在使用CROSS JOIN获取所有可能的对.如何仅过滤唯一的过滤器? CROSS JOIN返回所有id组合.例如101和102以及102和101.如何使用BigQuery减少所有反向双打并保持第一个得到对(101和102)?

I have a BigQuery table. I'm getting all possible pairs using CROSS JOIN. How can I filter unique ones only? CROSS JOIN returns all combos of ids. For example 101 and 102 as well as 102 and 101. How can I reduce all inversed doubles keeping first getting pair (101 and 102) using BigQuery?

    with t0 as (SELECT 1 as big_id, '101' as small_id,  0.99 as bottom, 1.03 top
      UNION ALL SELECT 1, 'z3Awwer', 1.05, 1.09
      UNION ALL SELECT 1, 'gA1sd03', 1.09, 1.13
      UNION ALL SELECT 1, 'sAs10sdf4', 1.2, 1.25
      UNION ALL SELECT 1, 'Adf105', 1.33, 1.39
      UNION ALL SELECT 2, 'A1sdf02', 1.05, 1.09
      UNION ALL SELECT 2, '345A103', 1.09, 1.13
      UNION ALL SELECT 2, 'A1sd04', 1.2, 1.25
      UNION ALL SELECT 2, '6_0Awe105', 1.33, 1.39)
SELECT * FROM t0
CROSS JOIN t0 t1 
WHERE t0.big_id = t1.big_id

推荐答案

不确定这是否基于真实数据，但是您可以添加过滤器以删除已加入的small_id:

Not sure if this is based on real data, but you could add a filter to drop out small_id's that have already been joined:

SELECT *
FROM t0
CROSS JOIN t0 t1 
WHERE t0.big_id = t1.big_id
  and t1.small_id >= t0.small_id

这可以确保101出现在102上，而102不会出现在101上.

This ensures that 101 will show up with a 102, but 102 will not show up with a 101.

这篇关于如何使用BigQuery获得唯一对?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！