在IBM netezza SQL表中发现muiltiple列的不同的cobinations的错误。

I need to count a distinct combination number for multiple columns in SQL IBM netteza Aiginity workbench.

我需要为SQL IBM netteza aigeness workbench中的多个列计算一个不同的组合数。

 id1   id2    id3    id4  id5   id6
 NY    63689  eiof   394  9761   9318
 NY    63689  secf   064  9742   78142
 CT    39631  pfef  92169 9418   9167
 CT    39631  bfbde  23457 10052  618146

The result should be

结果应该

 id1   id2    value
 NY    63689  2   
 CT    39631  2

I need to find how many distinct combinations of id3,id4,id5 ,id6 for each distinct id2 and id2.

我需要找到id3,id4,id5,id6对id2和id2的不同组合。

My sql query :

我的sql查询:

 SELECT id1, id2,  
   ( 
      SELECT count(*)  as dis_id_by_id1_id2
      from 
      (
        select distinct id3 , id4 ,id5 ,id6
        FROM my_table 
        group by id1, id2 
      ) t
   ) 
   from my_table
   group by id1, id2

I got error:

我得到了错误:

   id3 must be GROUPed or used in an aggregate function

If I grouped id3, id4, id5, id6. the result is wrong.

如果我将id3, id4, id5, id6分组。结果是错误的。

count(distinct id3, id4) is not allowed in IBM netezza.

IBM netezza中不允许计数(不同的id3, id4)。

Any help would be appreciated.

如有任何帮助，我们将不胜感激。

2 个解决方案

#1

If you first write a query to generate the unique combinations, then you can use it as a nested sub-query.

如果您首先编写一个查询来生成惟一的组合，那么您可以将它用作嵌套子查询。

Then it's a simple GROUP BY and COUNT().

然后是一个简单的GROUP BY和COUNT()。

SELECT
  id1,
  id2,
  COUNT(*)   AS value
FROM
(
  SELECT
    id1, id2, id3, id4, id5, id6
  FROM
    myTable
  GROUP BY
    id1, id2, id3, id4, id5, id6
)
  AS uniques
GROUP BY
  id1,
  id2

#2

In Postgres, you can do this with arrays and counts:

在Postgres中，您可以使用数组和计数进行此操作:

select id1, id2,
       count(distinct string_to_array(id3::text, '') || id4::text || id5::text || id6::text)
from table t
group by id1, id2;

#1