问题描述

  • 第一步，获取GUID：
SELECT guid, SUBSTRING_INDEX( SUBSTRING_INDEX( extra, 'idfa=', - 1 ), '&', 1 ) AS idfa FROM app_tiktok_install_list WHERE extra <> '';
  • 第二步： app_tiktok_install_list.guid= app_install_log.GUID 获取到app_install_log的GUID，SIEGUID,SIPEGUID；
  • 第三步：再根据获取GUID，SIEGUID,SIPEGUID获取到user_orders里的Commision；
  • 最后返回 第一步的guid，idfa，Commision这个sql怎么写？

解决方案

先使用子查询获取

        ail.GUID,

        ail.SIEGUID,

        ail.SIPEGUID,

        SUBSTRING_INDEX(SUBSTRING_INDEX(tt.extra, 'idfa=', -1), '&', 1) AS

在子查询中使用关联查询：ON ail.GUID = tt.guid， 然后将获取到的结果为表 t1，接着再使用关联查询关联查询表t1和表coupert_base.user_orders，最后将获取

t1.guid,

 t1.idfa,

 t2.Commission

SELECT
    t1.guid,
    t1.idfa,
    t2.Commission
FROM
    (
        SELECT
            ail.GUID,
            ail.SIEGUID,
            ail.SIPEGUID,
            SUBSTRING_INDEX(SUBSTRING_INDEX(tt.extra, 'idfa=', -1), '&', 1) AS idfa
        FROM
            coupert_base.app_install_log ail
        LEFT JOIN coupert_tracking.app_tiktok_install_list tt ON ail.GUID = tt.guid
        WHERE
            tt.extra <> ''
    ) AS t1
JOIN coupert_base.user_orders t2 ON t1.GUID = t2.GUID or t1.SIEGUID = t2.GUID or t1.SIPEGUID = t2.GUID
where t2.created_at between '2023-06-20' and '2023-07-31';

这里的数据中有重复的guid和idfa，所以我们使用group by进行分组求和：

SELECT
    t1.guid,
    t1.idfa,
    sum(t2.Commission) Commission
FROM
    (
        SELECT
            ail.GUID,
            ail.SIEGUID,
            ail.SIPEGUID,
            SUBSTRING_INDEX(SUBSTRING_INDEX(tt.extra, 'idfa=', -1), '&', 1) AS idfa
        FROM
            coupert_base.app_install_log ail
        LEFT JOIN coupert_tracking.app_tiktok_install_list tt ON ail.GUID = tt.guid
        WHERE
            tt.extra <> ''
    ) AS t1
JOIN coupert_base.user_orders t2 ON t1.GUID = t2.GUID or t1.SIEGUID = t2.GUID or t1.SIPEGUID = t2.GUID
where t2.created_at between '2023-06-20' and '2023-07-31' group by t1.guid, t1.idfa;