MySQL 从包含不同数据类型的列中过滤某些值

Question

有两个主要表。我正在提供必要的列。

设计师与

Category | deisgnID

印象与

recId | impressionType | impressionId | impressionAction | session_id

这是 sessions 中的示例 session 。 impressionId 是一个包含不同数据类型和值的多态列(我认为这就是术语；我可能是错的)。

recId   impressionType      impressionId    impressionAction    session_Id
73790   USER                    11182       LOGIN                   acbd1234
73791   UNDOCKED            abcd1234        UNDOCKED            acbd1234
73792   PRODUCT                 1446        TAPPED-WALL     acbd1234
73793   CARTS                   3586        ADDED           acbd1234
73794   CART-PRODUCT            14941       ADDED           acbd1234
73801   PRODUCT                 1465        TAPPED-RECOMMENDATION   acbd1234
73802   CART-PRODUCT            14942       ADDED           acbd1234
73811   PRODUCT                 1465        TAPPED-RECOMMENDATION   acbd1234
73818   PRODUCT                 1446        TAPPED-RECOMMENDATION   acbd1234
73828   PRODUCT                 1965        TAPPED-WALL     acbd1234
73829   CART-PRODUCT           14944        ADDED           acbd1234
73836   PRODUCT                 1952        TAPPED-WALL     acbd1234
73837   CART-PRODUCT            14945       ADDED           acbd1234
73882   PRODUCT                 502         TAPPED-WALL     acbd1234
73883   CART-PRODUCT            14949       ADDED           acbd1234
73897   CART-PRODUCT            14951       ADDED           acbd1234
73942   EMAILED_RECOMM          904         SEND            acbd1234
73943   EMAILED_RECOMM          1586        SEND            acbd1234
73944   EMAIL-NOTIFICATION  abcd@amazon.com SENDMAIL        acbd1234

我使用下面的代码合并了两个表

SELECT
         d.category
        ,d.designId
        ,i.session_id
        ,COUNT( IF(i.impressionAction = 'TAPPED'
                OR i.impressionAction = 'TAPPED-LISTPAGE'
                OR i.impressionAction = 'TAPPED-wall'
                OR i.impressionAction = 'TAPPED-RECOMMENDATION') AS SCANS_total
        ,COUNT(IF(i.impressionAction = 'TAPPED', 1, NULL)) AS TAPPED
        ,COUNT(IF(i.impressionAction = 'TAPPED-LISTPAGE', 1, NULL)) AS TAPPED_LISTPAGE
        ,COUNT(IF(i.impressionAction = 'TAPPED-WALL', 1, NULL)) AS TAPPED_WALL
        ,COUNT(IF(i.impressionAction = 'TAPPED-RECOMMENDATION', 1, NULL)) AS TAPPED_RECOMMENDATION
        ,COUNT(IF(i.impressionAction = 'SEND', 1, NULL)) AS IS_ITEM_SENT_BY_EMAIL

    FROM
        Impressions i 
        INNER JOIN designers d 
        ON i.impressionId = d.designId

    WHERE
           i.createDate >= '2014-06-18'
        AND HOUR(i.createDate) >= 10
        AND HOUR(i.createDate) < 21
        AND i.impressionId not like '%amazon.com%'

    GROUP BY
        i.session_id, i.impressionId
    HAVING 
        SCANS_total <> 0


    ORDER BY
        d.category, i.impressionId, SCANS_total desc 

基本上，我生成了一个列表，其中包含通过不同类型的扫描对产品进行扫描的次数，按类别 和designId 分割每 session 。

我的主要问题是:我无法使用 i.impressionId not like '%amazon.com%' 过滤掉某些电子邮件域，例如 amazon.com> 查询。

对于每个 session ，如果用户发送电子邮件，则在 impresseiondId 下有一个电子邮件地址，其中包含 impressionAction = SENDMAIL 和 impressionType = EMAIL-NOTIFICATION。

我试图通过使用 where ... i.impressionId not like '%amazon.com%' 从计算中完全过滤掉那些与特定域电子邮件的 session ，但事实并非如此工作。

有没有一种方法可以在我尝试执行操作时过滤掉某些电子邮件域？

任何想法将不胜感激!

更新

我今天醒来，意识到解决问题的子查询。这是我为过滤 session 而编写的子查询；但是，查询超时 并失败。

本质上，查询会生成电子邮件地址属于某个域的所有 session ，然后我否定这些 session 的存在。 关于如何优化它并让它发挥作用有什么想法吗？

where i.session_id NOT IN(
            SELECT session_id from Impressions 
            where impressionId LIKE '%amazon.com%')

Answer 1

我一直在不懈地尝试解决这个问题，尝试提出不同的解决方案(失败的解决方案请参阅更新)。

我觉得我有。如果有改进方法，请告诉我。

SELECT *
FROM 
(
SELECT
         d.category
        ,d.designId
        ,i.session_id
        ,i.createDate

        ,d.name
        ,d.productTitle
        ,d.colors


        ,COUNT( IF(i.impressionAction = 'TAPPED'
                OR i.impressionAction = 'TAPPED-LISTPAGE'
                OR i.impressionAction = 'TAPPED-wall'
                OR i.impressionAction = 'TAPPED-RECOMMENDATION') AS SCANS_total
        ,COUNT(IF(i.impressionAction = 'TAPPED', 1, NULL)) AS TAPPED
        ,COUNT(IF(i.impressionAction = 'TAPPED-LISTPAGE', 1, NULL)) AS TAPPED_LISTPAGE
        ,COUNT(IF(i.impressionAction = 'TAPPED-WALL', 1, NULL)) AS TAPPED_WALL
        ,COUNT(IF(i.impressionAction = 'TAPPED-RECOMMENDATION', 1, NULL)) AS TAPPED_RECOMMENDATION
        ,COUNT(IF(i.impressionAction = 'SEND', 1, NULL)) AS IS_ITEM_SENT_BY_EMAIL

    FROM
        Impressions i 
        INNER JOIN designers d 
        ON i.impressionId = d.designId

    WHERE
           i.createDate >= '2014-06-18'
        AND HOUR(i.createDate) >= 10
        AND HOUR(i.createDate) < 21

    GROUP BY
        i.session_id, i.impressionId
    HAVING 
        SCANS_total <> 0


    ORDER BY
        d.category, i.impressionId, SCANS_total desc 
    ) AS P
    LEFT JOIN 
    (
            SELECT session_id as sessionsToBeRemoved from Impressions 
            where impressionId LIKE '%amazon.com%'

            GROUP BY session_id
    ) AS U on P.session_id = U.sessionsToBeRemoved 
    WHERE U.sessionsToBeRemoved IS NULL