{"id":1176,"date":"2021-03-02T14:39:23","date_gmt":"2021-03-02T06:39:23","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=1176"},"modified":"2021-03-02T14:39:23","modified_gmt":"2021-03-02T06:39:23","slug":"%e5%85%b3%e8%81%94%e5%ad%97%e6%ae%b5null%e5%80%bc%e9%80%a0%e6%88%90%e9%83%a8%e5%88%86task%e8%bf%87%e6%85%a2","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=1176","title":{"rendered":"\u5173\u8054\u5b57\u6bb5 null \u503c\u9020\u6210\u90e8\u5206 task \u8fc7\u6162"},"content":{"rendered":"

\u6982\u8981\u63cf\u8ff0<\/h3>\n
\n

\u672c\u6587\u4e3b\u8981\u4ecb\u7ecd\u5728\u591a\u8868\u5173\u8054\u7684\u573a\u666f\u4e0b\uff0c\u5982\u4f55\u907f\u514d\u56e0\u4e3a\u4e3b\u8868 null \u503c\u5360\u6bd4\u8fc7\u591a\u800c\u5bfc\u81f4\u7684\u4e00\u4e2a reduce \u4efb\u52a1\u8fc7\u6162\u7684\u95ee\u9898<\/p>\n

\u8be6\u7ec6\u8bf4\u660e<\/h3>\n
\n

Inceptor\u57fa\u672c\u4f18\u5316\u601d\u8def\u4e2d\uff0c\u6211\u4eec\u5728\u505a join \u5173\u8054\u64cd\u4f5c\u7684\u65f6\u5019\uff0c\u5c3d\u91cf\u907f\u514d\u5173\u8054\u5b57\u6bb5\u6709 null \u503c\u548c\u7a7a\u5b57\u7b26\u4e32\uff0c\u4f1a\u5bfc\u81f4\u90e8\u5206 task \u6267\u884c\u8fc7\u6162\u3002
\n\u4e0b\u9762\u8fd9\u6761sql\u8fd0\u884c\u65f6\u95f4\u8f83\u957f\uff0c\u9700\u89816000s\uff0c\u662f\u4e0d\u6b63\u5e38\u7684<\/p>\n

create table temp.day_user_play_uuid_split_20180226_test1 as\nselect \nt.datess,\nt.device_id,\nt.version_id,\nt.province_id,\nt.city_id,\nt.telecom_id,\nt.terminal_id,\nt.ip,\ncase\nwhen play_type_id='02' then \ncase when t.uuid is not null and t.uuid !='' and t.uuid !='0' then t.uuid when t2.uuid is not null then t2.uuid when t3.uuid is not null then t3.uuid\nelse 'unknow' end\nwhen play_type_id='03' then\ncase when t.uuid is not null and t.uuid !='' and t.uuid !='0' then t.uuid when t1.premiere_channel_uuid is not null then t1.premiere_channel_uuid else 'unknow' end\nelse t.uuid end,\nt.play_type_id,\nt.play_type,\nt.program_id,\nt.program_serise_id,\nt.client_time,\nt.server_time,\nt.open_time,\nt.play_time,\nt.buffer_count,\nt.buffer_aver_time,\nt.url_first,\nt.url,\nt.terminal_type\nfrom\n    (select * from dm_bas.day_user_play_uuid where play_type<>'vod_replay') t\nleft join \n         dim.cms_program t1 on t.program_id=t1.program_id \nleft join \n         dim.cms_playbill_channel t2 on t.program_id=t2.channel_id \nleft join \n         dim.cms_history_playbill_channel t3 on t.program_id=t3.playbill_id ;<\/code><\/pre>\n
\u6392\u67e5\u601d\u8def<\/h4>\n
\n
\u67e5\u770b\u4e3b\u8868\u5173\u8054\u5b57\u6bb5\u5206\u5e03\uff0c\u53ef\u4ee5\u770b\u51fa\u7a7a\u503c\u5360\u603b\u91cf\u768466.6%\u7684\u767e\u5206\u6bd4\uff0c\u8fd9\u5bfc\u81f4\u5927\u90e8\u5206\u8ba1\u7b97\u5206\u914d\u5230\u4e00\u4e2areduce\u5bfc\u81f4\u6574\u4e2a\u4efb\u52a1\u8ba1\u7b97\u7f13\u6162\uff0c\u4f53\u73b0\u4e3areduce\u8fdb\u5ea6\u957f\u65f6\u95f4\u5904\u572899%\u3002<\/strong><\/p>\n
--\u603b\u6570\u636e\u91cf\n> select count(1) as num from dm_bas.day_user_play_uuid where  play_type<>'vod_replay';\n390270887\n--\u5173\u8054\u5b57\u6bb5\u5206\u5e03\u60c5\u51b5\n> select program_id,count(1) as num from dm_bas.day_user_play_uuid where  play_type<>'vod_replay' group by program_id order by num desc limit 6;\n            259816668\n0           46610174\n1           1284378\n70564740    357546\n70564743    251696\n70564694    212326<\/code><\/pre>\n
\u89e3\u51b3\u65b9\u6848<\/h4>\n
\n
\u5173\u8054\u524d\u5c06\u5de6\u8868\u4e3a\u7a7a\u5173\u8054\u5b57\u6bb5\u8bbe\u7f6e\u4e3a\u4e00\u4e2a\u968f\u673a\u6570\uff0c\u518d\u53bb\u5173\u8054\u53f3\u8868\uff0c\u628a\u503e\u659c\u7684\u6570\u636e\u5206\u5230\u4e0d\u540c\u7684reduce\u4e0a\uff0c\u7531\u4e8enull\u503c\u5173\u8054\u4e0d\u4e0a\uff0c\u5904\u7406\u540e\u5e76\u4e0d\u5f71\u54cd\u6700\u7ec8\u7ed3\u679c<\/strong><\/p>\n
create table temp.day_user_play_uuid_split_20180226_test3 as\nselect \nt.datess,\nt.device_id,\nt.version_id,\nt.province_id,\nt.city_id,\nt.telecom_id,\nt.terminal_id,\nt.ip,\ncase\nwhen play_type_id='02' then \ncase when t.uuid is not null and t.uuid !='' and t.uuid !='0' then t.uuid when t2.uuid is not null then t2.uuid when t3.uuid is not null then t3.uuid\nelse 'unknow' end\nwhen play_type_id='03' then\ncase when t.uuid is not null and t.uuid !='' and t.uuid !='0' then t.uuid when t1.premiere_channel_uuid is not null then t1.premiere_channel_uuid else 'unknow' end\nelse t.uuid end,\nt.play_type_id,\nt.play_type,\nt.program_id,\nt.program_serise_id,\nt.client_time,\nt.server_time,\nt.open_time,\nt.play_time,\nt.buffer_count,\nt.buffer_aver_time,\nt.url_first,\nt.url,\nt.terminal_type \nfrom \n   (select \n   datess,device_id,version_id,province_id,city_id,telecom_id,terminal_id,ip,uuid,play_type_id,play_type,\n   case when program_id is not null and trim(program_id) != '' then program_id else cast(RAND()*100000 as int) end as program_id,\n   program_serise_id,client_time,server_time,open_time,play_time,buffer_count,\n   buffer_aver_time,url_first,url,terminal_type\n   from dm_bas.day_user_play_uuid where play_type<>'vod_replay') t \nleft join \n        dim.cms_program t1 on t.program_id=t1.program_id \nleft join \n        dim.cms_playbill_channel t2 on t.program_id=t2.channel_id \nleft join \n        dim.cms_history_playbill_channel t3 on t.program_id=t3.playbill_id ;<\/code><\/pre>\n
 case when program_id is not null and trim(program_id) != '' then program_id else cast(RAND()*100000 as int) end as program_id,<\/code>
\n\u628aprogram_id\u4e3anull\u548c\u7a7a\u5b57\u7b26\u4e32\u7684\u8f6c\u6362\u4e3a\u968f\u673a\u6570\u5373\u53ef<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
\u6982\u8981\u63cf\u8ff0 \u672c\u6587\u4e3b\u8981\u4ecb\u7ecd\u5728\u591a\u8868\u5173\u8054\u7684\u573a\u666f\u4e0b\uff0c\u5982\u4f55\u907f\u514d\u56e0\u4e3a\u4e3b\u8868 null \u503c\u5360\u6bd4\u8fc7\u591a\u800c\u5bfc\u81f4\u7684\u4e00\u4e2a reduce \u4efb ..<\/p>\n
<\/div>\n
Read more<\/a><\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46],"tags":[],"class_list":["post-1176","post","type-post","status-publish","format-standard","hentry","category-performance"],"acf":[],"_links":{"self":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/1176","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1176"}],"version-history":[{"count":3,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/1176\/revisions"}],"predecessor-version":[{"id":4375,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/1176\/revisions\/4375"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1176"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1176"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1176"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}