sql - 选择具有特定属性的分组行

Question

我试图只选择包含特定属性的特定行。这是我正在处理的数据示例:

src_id                                              cand_source------                                              -----------201609-004d7bgNDFXuIrQPXwsXrOptt2PdTdeXsjV5RJ6_mEQ  mcp201609-004d7bgNDFXuIrQPXwsXrOptt2PdTdeXsjV5RJ6_mEQ  mc2201609-00WmbmuIp3cwAcTNTbrgb9tTVR0AKNf-RvjXcHWPEEQ  mc2201609-00WmbmuIp3cwAcTNTbrgb9tTVR0AKNf-RvjXcHWPEEQ  mc2201609-00WmbmuIp3cwAcTNTbrgb9tTVR0AKNf-RvjXcHWPEEQ  mc2201609-00WmbmuIp3cwAcTNTbrgb9tTVR0AKNf-RvjXcHWPEEQ  mc2201609-00WmbmuIp3cwAcTNTbrgb9tTVR0AKNf-RvjXcHWPEEQ  mc2201609-00WmbmuIp3cwAcTNTbrgb9tTVR0AKNf-RvjXcHWPEEQ  mc2201609-01My_orS795Hmomry3-JiCiBVimarRzRGQ9Cnornp8Q  mcp201609-01My_orS795Hmomry3-JiCiBVimarRzRGQ9Cnornp8Q  mcp201609-01My_orS795Hmomry3-JiCiBVimarRzRGQ9Cnornp8Q  mc2201609-01My_orS795Hmomry3-JiCiBVimarRzRGQ9Cnornp8Q  mcp201609-01My_orS795Hmomry3-JiCiBVimarRzRGQ9Cnornp8Q  mc2201609-01noPFGBCqbH9jUB9MHNqPynjqW8cr24LJY917vSGTs  mc2201609-01noPFGBCqbH9jUB9MHNqPynjqW8cr24LJY917vSGTs  mc2201609-02ISoPEX0VVkQ0ogot49Q-e7K39Zyk2vdN1rB4Q-kl0  mc2201609-02ISoPEX0VVkQ0ogot49Q-e7K39Zyk2vdN1rB4Q-kl0  mc2201609-02LVZ8UqAaz7JCp3RAOTiIE7zH2mveiSQPBo6I6dHDc  mc2201609-02LVZ8UqAaz7JCp3RAOTiIE7zH2mveiSQPBo6I6dHDc  mc2201609-03dLH32kaKYVwIj4HiT1tZjCNgqgXiG-fvezX3S9QI4  mc2201609-03dLH32kaKYVwIj4HiT1tZjCNgqgXiG-fvezX3S9QI4  mc2201609-0421Jatpsk9T8GOD1M_GvDrnyV4dA41IL5tDeuTxGwU  mc2201609-0421Jatpsk9T8GOD1M_GvDrnyV4dA41IL5tDeuTxGwU  mc2201609-0421Jatpsk9T8GOD1M_GvDrnyV4dA41IL5tDeuTxGwU  mc2201609-0421Jatpsk9T8GOD1M_GvDrnyV4dA41IL5tDeuTxGwU  mc2201609-0421Jatpsk9T8GOD1M_GvDrnyV4dA41IL5tDeuTxGwU  mc2201609-0421Jatpsk9T8GOD1M_GvDrnyV4dA41IL5tDeuTxGwU  mc2201609-0421Jatpsk9T8GOD1M_GvDrnyV4dA41IL5tDeuTxGwU  mc2201609-0421Jatpsk9T8GOD1M_GvDrnyV4dA41IL5tDeuTxGwU  mc2201609-0421Jatpsk9T8GOD1M_GvDrnyV4dA41IL5tDeuTxGwU  mc2201609-04HzM6NBIx_6QN91xzF9_p0RGfAQcRMeEhVFEPFZ8p4  mcp201609-04HzM6NBIx_6QN91xzF9_p0RGfAQcRMeEhVFEPFZ8p4  mc2201609-04HzM6NBIx_6QN91xzF9_p0RGfAQcRMeEhVFEPFZ8p4  mc2201609-04HzM6NBIx_6QN91xzF9_p0RGfAQcRMeEhVFEPFZ8p4  mc2201609-04HzM6NBIx_6QN91xzF9_p0RGfAQcRMeEhVFEPFZ8p4  mc2201609-04HzM6NBIx_6QN91xzF9_p0RGfAQcRMeEhVFEPFZ8p4  mc2201609-04HzM6NBIx_6QN91xzF9_p0RGfAQcRMeEhVFEPFZ8p4  mc2201609-04HzM6NBIx_6QN91xzF9_p0RGfAQcRMeEhVFEPFZ8p4  mc2201609-04HzM6NBIx_6QN91xzF9_p0RGfAQcRMeEhVFEPFZ8p4  mc2201609-04JzR3AMxsfQvAeq1MAgjCtMhcaqt2Z_WNmuUlYLrLM  mc2201609-04JzR3AMxsfQvAeq1MAgjCtMhcaqt2Z_WNmuUlYLrLM  mcp

What I want to do is select only the src_ids having at least one cand_source equaling mcp. Here's what I've tried:

SELECT *
FROM schema.table
WHERE src_id IN (
    SELECT src_id
    FROM schema.table
    WHERE batch_id = ?
    GROUP BY src_id
    HAVING count(cand_source = 'mcp') > 1
)
ORDER BY src_id,
    match_score DESC

然而，这不断给我返回 src_id 的集群，其中没有 cand_source 等于 mcp。

---

有人指出我只是把事情复杂化了。这是解决方案:

SELECT *
FROM schema.table
WHERE src_id IN (
    SELECT DISTINCT src_id
    FROM schema.table
    WHERE batch_id = ?
        AND cand_source = 'mcp'
)
ORDER BY src_id,
    match_score DESC

Answer 1

如果您只是想要具有 mcp 的 src_id，那么使用 WHERE 子句的直接查询就足够了，不需要条件聚合或任何东西。

SELECT DISTINCT 
    src_id
FROM
    Table
WHERE
    cand_source = 'mcp'
    AND batch_id = ?

如果您想要每个至少有 1 个 cand_source 的 src_id 的所有记录，您可以将其连接回表以接收所有记录。

SELECT t.*
FROM
    Table t
INNER JOIN 
    (SELECT DISTINCT src_id
     FROM Table
     WHERE cand_source = 'mcp'
       AND batch_id = ? ) d ON t.src_id = d.src_id
                            AND t.batch_id = ?

或者您可以使用具有出色窗口函数的公用表表达式来完成此操作。

WITH cte AS 
(
    SELECT *, COUNT(CASE WHEN cand_source = 'mcp' THEN cand_source END) OVER (PARTITION BY src_id) as McpCount
    FROM
       Table
    WHERE
       batch_id = ?

)
SELECT *
FROM
    cte
WHERE
    McpCount > 0;