How to count number of distinct pairs per partition in SQL?-CodePudding

I want to get the # of distinct pair combinations per level in my table.

Sample Data:

level       group_no    item_no
oiegown     1           1
oiegown     1           2
oiegown     1           2
oiegown     1           3
oiegown     2           1
wefwefw     1           1
wefwefw     2           2

My Attempt:

Wanted to do something like

COUNT(DISTINCT group_no, item_no) 
  OVER (PARTITION BY level)
AS item_count

Expected Output:

level       item_count
oiegown     4           
wefwefw     2

But seems that COUNT() only accepts one argument in BigQuery. How can I modify my query to get my desired result?

CodePudding user response：

You can join both column into one Text, and count the concatenated text

COUNT(DISTINCT CONCAT(group_no, item_no)) 
  OVER (PARTITION BY level)
AS item_count

CodePudding user response：

Use below query for the expected out:

SELECT level, COUNT(DISTINCT group_no || item_no) AS item_count
  FROM sample_table
 GROUP BY 1;