如何避免 - 用户定义的聚合不能与其他不同的聚合一起在查询中使用

Question

我有错误

[0A000][5366] [Vertica]VJDBC 错误：用户定义的聚合无法在与其他不同聚合的查询中使用

[0A000][5366] [Vertica]VJDBC 错误：当我执行时，

SELECT LISTAGG(DISTINCT a USING PARAMETERS separator=',', max_length = 10) AS a,
       COUNT(DISTINCT b)                                                   AS b
FROM t

我是否有办法避免此聚合并具有相同的逻辑？

在实际项目中，我有一个复杂的查询，其中包含许多“子选择”和“with as”、“group by”逻辑，所以我想知道如何重构，我有多少种方式？

我阅读了链接，但不知道如何组合 COUNT DISTINCT 和 LISTAGG DISTINCT： https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm" vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm

DB：Vertica 分析数据库 v10.1.1-3

UDP：@Kendle 帮助下的解决方案

(WITH zz as (SELECT a, b FROM mkt_dwh.t) -- "where", "group by" logic
 SELECT a, b
 FROM (WITH aa AS (SELECT DISTINCT a FROM zz),
            bb AS (SELECT COUNT(b) as b FROM zz)
       SELECT LISTAGG(distinct aa.a USING PARAMETERS separator=',', max_length = 1000) AS a,
              bb.b                                                                     AS b
       FROM aa,
            bb
       GROUP BY bb.b) as test);

CREATE TABLE t
(
    a VARCHAR,
    b VARCHAR
);


INSERT INTO mkt_dwh.t
VALUES ('1', '1');
INSERT INTO mkt_dwh.t
VALUES ('1', '1');
INSERT INTO mkt_dwh.t
VALUES ('2', '2');
INSERT INTO mkt_dwh.t
VALUES ('2', '3');
INSERT INTO mkt_dwh.t
VALUES ('3', '3');

SELECT LISTAGG(a USING PARAMETERS separator=',', max_length = 1000) AS a,
       COUNT(distinct b)                                            AS b
FROM mkt_dwh.t;
select count(*)
FROM mkt_dwh.t;

SELECT LISTAGG(distinct aa.a USING PARAMETERS separator=',', max_length = 1000) AS a,
       COUNT(bb.b)                                                              AS b
FROM (SELECT DISTINCT a FROM mkt_dwh.t) aa,
     (SELECT DISTINCT b FROM mkt_dwh.t) bb;

(WITH zz as (SELECT a, b FROM mkt_dwh.t) -- some where, group by logic
 SELECT a, b
 FROM (WITH aa AS (SELECT DISTINCT a FROM zz),
            bb AS (SELECT COUNT(b) as b FROM zz)
       SELECT LISTAGG(distinct aa.a USING PARAMETERS separator=',', max_length = 1000) AS a,
              bb.b                                                                     AS b
       FROM aa,
            bb
       GROUP BY bb.b) as test);

Answer 1

您可以使用子查询来进行区分。

SELECT LISTAGG(aa.a USING PARAMETERS separator=',', max_length = 10) AS a,
       COUNT(bb.b)                                                   AS b
FROM 
  (SELECT DISTINCT a FROM t) aa,
  (SELECT DISTINCT b FROM t) bb;