logo标签列表

Postgres按组分组并提取具有多个元素的组

sqlpostgresql
4
我希望您能以Unicode字段（keyword_text和keyword_match_type）分组，并提取拥有两个以上元素的分组的所有列和所有行。请参考以下示例：
例如，一个行如下：
keyword_text | keyword_norm | keyword_GAD_id| keyword_account  | keyword_MCC_id | keyword_campaign | keyword_campaign_GAD_id | keyword_ad_group | keyword_ad_group_GAD_id| keyword_destination_url | keyword_max_cpc | keyword_status | keyword_match_type | keyword_campaign_status | keyword_ad_group_status | db_id | created_at |
________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
"lebanese home delivery jai", "lebanese home delivery jai", 61557127036, "IN [S_02] Cuisine", 7795189055, "IN-JAI[S[Cui_30_EN]: Lebanese", 301573516, "IN-JAI[S[Cui_30_EN|del_02|geo_01]_ex: (Lebanese) Lebanese home delivery Jaipur", 11043049036, http://www.bla.in/restaurants/index/cuisines/lebanese/city/jaipur, 480000, ENABLED, EXACT, PAUSED, PAUSED, 1, "2014-07-18 18:42:43"

表格是使用以下代码创建的：

CREATE TABLE adword_keywords
(
  keyword_text character varying(1000) NOT NULL,
  keyword_norm character varying(1000) NOT NULL,
  "keyword_GAD_id" bigint NOT NULL,
  keyword_account character varying NOT NULL,
  "keyword_MCC_id" bigint NOT NULL,
  keyword_campaign character varying NOT NULL,
  "keyword_campaign_GAD_id" bigint NOT NULL,
  keyword_ad_group character varying NOT NULL,
  "keyword_ad_group_GAD_id" bigint NOT NULL,
  keyword_destination_url character varying NOT NULL,
  keyword_max_cpc double precision,
  keyword_status keyword_status,
  keyword_match_type match_type,
  keyword_campaign_status keyword_c_status,
  keyword_ad_group_status keyword_ag_status,
  db_id bigserial NOT NULL,
  created_at timestamp without time zone,
  CONSTRAINT adword_keywords_pkey PRIMARY KEY (db_id)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX ix_adword_keywords_keyword_norm
  ON adword_keywords
  USING btree
  (keyword_norm COLLATE pg_catalog."default");

我尝试了以下查询：

SELECT adword_keywords.*
FROM adword_keywords 
    JOIN (
        SELECT adword_keywords.keyword_text AS keyword_text,adword_keywords.keyword_match_type AS keyword_match_type 
        FROM adword_keywords GROUP BY adword_keywords.keyword_text, adword_keywords.keyword_match_type 
        HAVING count(adword_keywords.db_id) > 1) AS anon_1 
    ON adword_keywords.keyword_text = anon_1.keyword_text AND adword_keywords.keyword_match_type = anon_1.keyword_match_type 
WHERE adword_keywords.keyword_campaign_status = 'ENABLED' AND adword_keywords.keyword_ad_group_status = 'ENABLED' AND adword_keywords.keyword_status = 'ENABLED'

很遗憾，这个查询返回了错误的结果。这也意味着在以 ['keyword_text'，'match_type'] 为分组依据时，由一个元素组成的组也是如此！

有人知道这个查询出了什么问题吗？

请注意，如果我从数据库中提取所有数据并将其放入pandas数据结构中，则可以使用以下查询：

SELECT * FROM adword_keywords  
WHERE adword_keywords.keyword_campaign_status = \'ENABLED\' 
AND adword_keywords.keyword_ad_group_status = \'ENABLED\' 
AND adword_keywords.keyword_status = \'ENABLED\'

我可以按照以下方式筛选我想要的群组：
df.groupy(['keyword_text','match_type']).filter(lambda x: x.shape[0]>1)

这种后续过程返回了正确的结果。

然而，出于性能和内存问题的原因（数据集很大，无法完全加载到RAM中），我想使用相同的SQL查询。

编辑

根据ypercube的答案，有三个替代查询可以返回正确的结果。我已经收集了它们以供参考，并列出了它们的运行时间：第一个版本是最快的。

使用EXISTS，1个循环，3个中的最佳：每个循环2.22秒：

WITH cte AS
  ( SELECT * 
    FROM adword_keywords  
    WHERE keyword_campaign_status = 'ENABLED' 
      AND keyword_ad_group_status = 'ENABLED' 
      AND keyword_status = 'ENABLED'
  )
SELECT a.*
FROM cte AS a
WHERE EXISTS
      ( SELECT *
        FROM cte AS b
        WHERE (b.keyword_text, b.keyword_match_type) 
            = (a.keyword_text, a.keyword_match_type)
          AND b.db_id <> a.db_id
      ) ;

使用PARTITION，1个循环，3次测试的最佳结果：每个循环5.7秒

WITH cte AS
  ( SELECT *,
           COUNT(*) OVER (PARTITION BY keyword_text, keyword_match_type) AS cnt 
    FROM adword_keywords  
    WHERE (keyword_campaign_status, keyword_ad_group_status, keyword_status)
        = ('ENABLED', 'ENABLED', 'ENABLED')
  )
SELECT *
FROM cte
WHERE cnt >= 2 ;

使用GROUP BY，1次循环，3次中最佳：每个循环5.11秒：
select ak.*
from
    adword_keywords ak
    inner join (
        select keyword_text, keyword_match_type
        from adword_keywords
        where
            keyword_campaign_status = 'ENABLED' AND
            keyword_ad_group_status = 'ENABLED' AND
            keyword_status = 'ENABLED'
        group by keyword_text, keyword_match_type
        having count(db_id) > 1
    ) an1 using (keyword_text, keyword_match_type)
    where
            keyword_campaign_status = 'ENABLED' AND
            keyword_ad_group_status = 'ENABLED' AND
            keyword_status = 'ENABLED'
1
你能提供一些示例数据来说明这个问题吗？ - Gordon Linoff
3个回答
2
你可以使用 EXISTS 进行此类查询，因此根本不需要 COUNT，只需检查是否存在至少另一行具有相同的 campaign_status 和 ad_group_status。对主键的检查仅是为了确保它是另一行：
WITH cte AS
  ( SELECT * 
    FROM adword_keywords  
    WHERE (keyword_campaign_status, keyword_ad_group_status, keyword_status)
        = ('ENABLED', 'ENABLED', 'ENABLED')
  )
SELECT a.*
FROM cte AS a
WHERE EXISTS
      ( SELECT *
        FROM cte AS b
        WHERE (b.keyword_text, b.keyword_match_type) 
            = (a.keyword_text, a.keyword_match_type)
          AND b.db_id <> a.db_id
      ) ;

或者窗口函数:
WITH cte AS
  ( SELECT *,
           COUNT(*) OVER (PARTITION BY keyword_text, keyword_match_type) AS cnt 
    FROM adword_keywords  
    WHERE (keyword_campaign_status, keyword_ad_group_status, keyword_status)
        = ('ENABLED', 'ENABLED', 'ENABLED')
  )
SELECT *
FROM cte
WHERE cnt > 1 ;

您的查询未能成功，因为只在外层使用了ENABLED条件。将其添加到内部衍生表中应该会得到相同的结果：

SELECT ak.*
FROM
    adword_keywords ak
  JOIN
    ( SELECT keyword_text, keyword_match_type
      FROM adword_keywords
      WHERE (keyword_campaign_status, keyword_ad_group_status, keyword_status)
            = ('ENABLED', 'ENABLED', 'ENABLED')
      GROUP BY keyword_text, keyword_match_type
      HAVING COUNT(*) > 1
    ) AS d
    USING (keyword_text, keyword_match_type) 
WHERE (ak.keyword_campaign_status, ak.keyword_ad_group_status, ak.keyword_status)
    = ('ENABLED', 'ENABLED', 'ENABLED');
谢谢！第一个版本似乎返回了正确的结果：您能否评论一下您的查询和我的查询之间的区别？谢谢！特别是我没有在您的查询中看到任何count >1参数，但结果似乎是正确的。 - Luca Fiaschi
@Luca，您的查询在内部（派生）表中没有='ENABLED'条件。否则它也可以正常工作（就像Clodoaldo所做的那样，但是将条件放在内部，而不仅仅是移动它们。您确定那样不起作用吗？） - ypercubeᵀᴹ
@Clodoaldo，是的，Clodoaldo所做的也返回了错误的行数，具体来说是我的原始查询的>>。而您的查询返回了正确的行数<，比我的原始查询少。 - Luca Fiaschi
顺便提一下，还使用窗口函数，返回了正确的结果。 - Luca Fiaschi
0

你想要的是将过滤器放置在计数查询中吗？

select ak.*
from
    adword_keywords ak
    inner join (
        select keyword_text, keyword_match_type
        from adword_keywords
        where
            keyword_campaign_status = 'ENABLED' AND
            keyword_ad_group_status = 'ENABLED' AND
            keyword_status = 'ENABLED'
        group by keyword_text, keyword_match_type
        having count(*) > 1
    ) an1 using (keyword_text, keyword_match_type)

没有样本数据和期望结果，这只是猜测。

返回了错误的结果，比我的原始查询多出了许多行。 - Luca Fiaschi
1
我认为 ENABLED 的条件应该在外部层面上也存在。 - ypercubeᵀᴹ
如果你在外部层面上复制了由ypercube提出的“启用”条件，那么这个程序就能够工作。 - Luca Fiaschi
0

当你使用GROUP BY对某些字段进行分组时，你正在做两件重要的事情：

  1. 你在说你想要行，其中这些字段的组合是不同的。
  2. 你在说你要聚合你使用的所有其他字段。

这里可能会有一个“其他”问题。 如果你按字段分组，则无法像你想要的那样合并该字段。

相反，你可以数一些其他字段，比如你的主键。（我猜你也可以说COUNT(*) -- 实际上，如果你没有任何唯一字段，你必须这么做。）

例如，你的查询可能看起来像：

SELECT *
FROM adword_keywords
    JOIN (
        SELECT keyword_text keyword_match_type 
        FROM adword_keywords
        GROUP BY keyword_text, keyword_match_type
        HAVING count(keyword_id) > 1
    ) AS duplicated USING (keyword_text, keyword_match_type)
WHERE keyword_campaign_status = 'ENABLED'
  AND keyword_ad_group_status = 'ENABLED'
  AND keyword_status = 'ENABLED'

假设您想查找所有“已启用”状态的记录，其中至少有一个重复项——无论该重复项是否启用。如果您只想要具有启用重复项的记录，则需要将这些条件添加到子查询中。(此时，在内部联接消除非启用行后，您在外部查询中不再需要它们。）

对于以后的参考：如果您希望根本不允许出现重复项（这通常是错误的），则可以考虑在(keyword_text, keyword_match_type)上添加唯一键。

我明白你的观点，但计算主键“db_id”似乎没有任何区别。我已经编辑了问题，提供了一些数据示例。此外，将WHERE子句移动到内部选择中，奇怪地导致返回更多的行...这有点奇怪，因为启用的kws远少于所有可用的kws... - Luca Fiaschi
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接