编辑代码

SELECT '${stat_date}' count_date,
                      coalesce(t1.new_knowledge_shop, 0) new_knowledge_shop,
                      coalesce(t2.new_pay_knowledge_shop, 0) new_pay_knowledge_shop,
                      coalesce(t3.active_knowledge_shop, 0) active_knowledge_shop,
                      coalesce(t4.active_pay_Knowledge_shop, 0) active_pay_Knowledge_shop,
                      coalesce(t5.total_knowledge_shop, 0) total_knowledge_shop,
                      coalesce(t6.total_pay_knowledge_shop, 0) total_pay_knowledge_shop,
                      coalesce(t7.new_knowledge_shop_user, 0) new_knowledge_shop_user,
                      coalesce(t8.active_knowledge_shop_user, 0) active_knowledge_shop_user,
                      coalesce(t9.total_knowledge_shop_user, 0) total_knowledge_shop_user,
                      coalesce(NULL, 0) new_teacher_knowledge_shop,
                      coalesce(NULL, 0) active_teacher_knowledge_shop,
                      coalesce(t10.total_teacher_knowledge_shop, 0) total_teacher_knowledge_shop,
                      coalesce(NULL, 0) GMV
        FROM
          (SELECT count(DISTINCT app_id) new_knowledge_shop,
                  '${statdate}' statdate
           FROM db_result.r_app_info_dt
           WHERE statdate = '${statdate}'
             AND app_type = 1
             AND app_created_at LIKE '${stat_date}%' ) t1
        LEFT JOIN
          (SELECT count(DISTINCT app_id) new_pay_knowledge_shop,
                  '${statdate}' statdate
           FROM db_middle.dwd_app_extend_info_dt
           WHERE statdate = '${statdate}'
             AND app_type = 1
             AND app_created_at LIKE '${stat_date}%'
             AND first_pay_version_time LIKE '${stat_date}%' ) t2 ON t1.statdate = t2.statdate
        LEFT JOIN
          (SELECT count(DISTINCT a.app_id) active_knowledge_shop,
                  '${statdate}' statdate
           FROM
             (SELECT app_id
              FROM db_result.r_app_info_dt
              WHERE statdate = '${statdate}'
                AND app_type = 1) a
           LEFT JOIN
             (SELECT app_id
              FROM db_middle.dwd_app_active_log_d
              WHERE statdate = '${statdate}'
                AND created_at LIKE '${stat_date}%' ) b ON a.app_id = b.app_id
           WHERE b.app_id IS NOT NULL ) t3 on t1.statdate = t3.statdate
        LEFT JOIN
          (SELECT count(DISTINCT c.app_id) active_pay_Knowledge_shop,
                  '${statdate}' statdate
           FROM
             (SELECT app_id
              FROM db_middle.dwd_app_extend_info_dt
              WHERE statdate = '${statdate}'
                AND app_type = 1
                AND is_paid_app = 1 ) c
           LEFT JOIN
             (SELECT app_id
              FROM db_middle.dwd_app_active_log_d
              WHERE statdate = '${statdate}'
                AND created_at LIKE '${stat_date}%' ) d ON c.app_id = d.app_id
           WHERE d.app_id IS NOT NULL ) t4 on t1.statdate = t4.statdate
        LEFT JOIN
          (SELECT count(DISTINCT app_id) total_knowledge_shop,
                  '${statdate}' statdate
           FROM db_result.r_app_info_dt
           WHERE statdate = '${statdate}'
             AND app_type = 1 ) t5 on t1.statdate = t5.statdate
        LEFT JOIN
          (SELECT count(DISTINCT app_id) total_pay_knowledge_shop,
                  '${statdate}' statdate
           FROM db_middle.dwd_app_extend_info_dt
           WHERE statdate = '${statdate}'
             AND is_paid_app = 1
             AND app_type = 1 ) t6 on t1.statdate = t6.statdate
        LEFT JOIN
          (SELECT count(DISTINCT f.user_id) new_knowledge_shop_user,
                  '${statdate}' statdate
           FROM
             (SELECT app_id
              FROM db_result.r_app_info_dt
              WHERE statdate = '${statdate}'
                AND app_type = 1 ) e
           LEFT JOIN
             (SELECT app_id,
                     user_id
              FROM db_middle.m_db_ex_business_t_users_full
              WHERE statdate = '${statdate}'
                AND created_at LIKE '${stat_date}%') f ON e.app_id = f.app_id
           WHERE f.app_id IS NOT NULL ) t7 on t1.statdate = t7.statdate
        LEFT JOIN
          (SELECT count(DISTINCT h.user_id) active_knowledge_shop_user,
                  '${statdate}' statdate
           FROM
             (SELECT app_id
              FROM db_result.r_app_info_dt
              WHERE statdate = '${statdate}'
                AND app_type = 1 ) g
           LEFT JOIN
             (SELECT app_id,
                     user_id
              FROM db_middle.dwd_c_page_detail_d
              WHERE statdate = '${statdate}'
                AND visited_at LIKE '${stat_date}%' ) h ON g.app_id = h.app_id
           WHERE h.app_id IS NOT NULL ) t8 on t1.statdate = t8.statdate
        LEFT JOIN
          (SELECT count(DISTINCT k.user_id) total_knowledge_shop_user,
                  '${statdate}' statdate
           FROM
             (SELECT app_id
              FROM db_result.r_app_info_dt
              WHERE statdate = '${statdate}'
                AND app_type = 1 ) j
           LEFT JOIN
             (SELECT app_id,
                     user_id
              FROM db_middle.m_db_ex_business_t_users_full
              WHERE statdate = '${statdate}' ) k ON j.app_id = k.app_id
           WHERE k.app_id IS NOT NULL ) t9 on t1.statdate = t9.statdate
        LEFT JOIN
          (SELECT count(DISTINCT user_id) total_teacher_knowledge_shop,
                  '${statdate}' statdate
           FROM db_source.s_db_ex_alive_t_alive_role_dt
           WHERE statdate = '${statdate}'
             AND is_current_lecturer = 1
             AND STATE = 0 ) t10 on t1.statdate = t10.statdate