编辑代码

SELECT app_id,
       product_id,
       (CASE
               WHEN type = 1 THEN '音频'
               WHEN type = 2 THEN '视频'
               WHEN type = 3 THEN '图文'
               WHEN type = 4 THEN '会员'
               WHEN type = 5 THEN '电子书'
               WHEN type = 6 THEN '活动'
               WHEN type = 7 THEN '超级会员'
               WHEN type = 8 THEN '实物商品'
               WHEN type = 9 THEN '训练营'
               WHEN type = 10 THEN '直播'
               WHEN type = 11 THEN '专栏'
               WHEN type = 12 THEN '大专栏'
               WHEN type = 13 THEN '面课'
               WHEN type = 14 THEN '小班课'
               WHEN type = 15 THEN '大班课'
               WHEN type = 16 THEN 'AI互动课'
               ELSE '未知'
           END) AS type,
       created_at
FROM db_middle.m_all_knowledge_goods_dt
WHERE app_id = 'apprnDA0ZDw4581'
  AND statdate = '20210714';
  
SELECT a.shop_id ,a.type,
       count(a.resource_id)
FROM (
SELECT shop_id,
       resource_id,
       (CASE
            WHEN resource_type = 1 THEN '音频'
            WHEN resource_type = 2 THEN '视频'
            WHEN resource_type = 3 THEN '图文'
            WHEN resource_type = 4 THEN '会员'
            WHEN resource_type = 5 THEN '电子书'
            WHEN resource_type = 6 THEN '活动'
            WHEN resource_type = 7 THEN '超级会员'
            
            ELSE '未知'
        END) AS TYPE,
       created_at
FROM db_middle.dwd_t_column_dt
WHERE shop_id IN ('apptjx4cdef8282',
                 'apps4jqifhv9642',
                 'apppdlm2ds45382',
                 'appnCzGOMDI5973',
                 'app38itOR341547',
                 'app1g5f4c7r8050',
                 'appSlyA3Gx99074',
                 'appxnj6zdcc5861',
                 'appemdotokh8408',
                 'appncgcu3og2583',
                 'appPYtT9SmK6848',
                 'appemap6zkn9492',
                 'app0UiDkJfJ7805',
                 'appdfbe5ma05271',
                 'apprnDA0ZDw4581',
                 'appubzkicrv3414',
                 'appifPxpr2m1915',
                 'appyDrhTvOC3785',
                 'appXsLT9k7W7506',
                 'appwo8v3snx2907',
                 'app800svtqw3892',
                 'applkXXU1Ut3271',
                 'appnnpnjnit2178',
                 'applQYDQnx88025',
                 'appf334gafh9554',
                 'appXNcoHbFw8498',
                 'appqyyrrday3568',
                 'app1jdmSsle2347',
                 'appvg0zmkr41262',
                 'appglqtonso1528',
                 'appv4qem2iv5066',
                 'app9Iz5Opbs4373',
                 'apptM3HQ9i69329',
                 'appBoeyvwGM4777',
                 'appsiont5mh6882',
                 'appGHEgYyDX7666',
                 'appswwqhzki5491',
                 'appzy6u3nu35446',
                 'app2gxqbqar2815',
                 'appa8qhiixd7101',
                 'appkvkzwokf7773',
                 'app6FJiedDW7101',
                 'appmrmdbor42882',
                 'appzbhmhyqt2328',
                 'app7i0boaxz9333',
                 'appfwlgqkce2831',
                 'appynnyzxrr2900',
                 'appba8kuroq8800',
                 'appk8afqd4t7550',
                 'appnmfukijz8375',
                 'apprhnvuitn2954',
                 'appptjztmll8208',
                 'appxtbknzcm6257',
                 'appelb6huhp1949',
                 'appuflz2vdd2618',
                 'appufdyvduu1698',
                 'appkzjdjkve8131',
                 'app0t1qtgh47979',
                 'appvh0rapez6277',
                 'appttjwro9p2070',
                 'appnlwpAMFq1645',
                 'appfy5ra0fm3037',
                 'appxn9z7bsj6108',
                 'apprz4rqegg9439',
                 'appnmboh2ul1103',
                 'appuvvfaeg99388',
                 'appos4x4v8t4403',
                 'appje2nvwlc3930',
                 'appnidjyzvg1029',
                 'app8skcnqt43626',
                 'apppm8khbxm4193',
                 'appscx5jabp5366',
                 'appgwpz4flc3200',
                 'appls4s9den1255',
                 'appji61w9ad3830',
                 'appfwiuy0vo6496',
                 'app1vKZzcmv3428',
                 'appPXWAL76r6187',
                 'apprnfnii0n5661',
                 'app9drss6rq5108',
                 'appr43xtgq02291',
                 'appmrcTw3JB6874',
                 'appnrudob7g3293',
                 'appupcamcqe2427',
                 'appxkicqspp4633',
                 'appsyjv2vg64701',
                 'apph5au4wg53975',
                 'appvlwfst9d8325',
                 'appndkpp3tq3754',
                 'appjmngrdue4468',
                 'apppuin4qql2596',
                 'app4whhukys2984',
                 'appcNXPFzkm2244')
  AND statdate = '20210713'

) a
GROUP BY a.shop_id,a.type
order BY a.shop_id;