{"id":1706,"date":"2023-08-25T17:27:51","date_gmt":"2023-08-25T09:27:51","guid":{"rendered":"https:\/\/zhang.mba\/?p=1706"},"modified":"2023-08-25T17:29:16","modified_gmt":"2023-08-25T09:29:16","slug":"da-shu-jusql-mian-shi-ti","status":"publish","type":"post","link":"https:\/\/zhang.mba\/index.php\/2023\/08\/25\/17\/27\/51\/1706\/da-shu-jusql-mian-shi-ti\/database\/zhangzhiqi\/","title":{"rendered":"\u5927\u6570\u636eSQL\u9762\u8bd5\u9898"},"content":{"rendered":"

<\/span><\/a>\u4e00\u3001\u884c\u5217\u8f6c\u6362<\/h2>\n

\u63cf\u8ff0<\/strong>\uff1a\u8868\u4e2d\u8bb0\u5f55\u4e86\u5404\u5e74\u4efd\u5404\u90e8\u95e8\u7684\u5e73\u5747\u7ee9\u6548\u8003\u6838\u6210\u7ee9\u3002
\n\u8868\u540d\uff1at1<\/code>
\n\u8868\u7ed3\u6784\uff1a<\/p>\n

a -- \u5e74\u4efd\nb -- \u90e8\u95e8\nc -- \u7ee9\u6548\u5f97\u5206\n<\/code><\/pre>\n
\u8868\u5185\u5bb9<\/strong>\uff1a<\/p>\n
 a   b  c\n2014  B  9\n2015  A  8\n2014  A  10\n2015  B  7\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u591a\u884c\u8f6c\u591a\u5217<\/h4>\n
\u95ee\u9898\u63cf\u8ff0<\/strong>\uff1a\u5c06\u4e0a\u8ff0\u8868\u5185\u5bb9\u8f6c\u4e3a\u5982\u4e0b\u8f93\u51fa\u7ed3\u679c\u6240\u793a\uff1a<\/p>\n
 a  col_A col_B\n2014  10   9\n2015  8    7\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n    a,\n    max(case when b="A" then c end) col_A,\n    max(case when b="B" then c end) col_B\nfrom t1\ngroup by a;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e8c\uff1a\u5982\u4f55\u5c06\u7ed3\u679c\u8f6c\u6210\u6e90\u8868\uff1f\uff08\u591a\u5217\u8f6c\u591a\u884c\uff09<\/h4>\n
\u95ee\u9898\u63cf\u8ff0<\/strong>\uff1a\u5c06\u95ee\u9898\u4e00<\/em>\u7684\u7ed3\u679c\u8f6c\u6210\u6e90\u8868\uff0c\u95ee\u9898\u4e00\u7ed3\u679c\u8868\u540d\u4e3at1_2<\/code>\u3002<\/p>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n    a,\n    b,\n    c\nfrom (\n    select a,"A" as b,col_a as c from t1_2 \n    union all \n    select a,"B" as b,col_b as c from t1_2  \n)tmp; \n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e09\uff1a\u540c\u4e00\u90e8\u95e8\u4f1a\u6709\u591a\u4e2a\u7ee9\u6548\uff0c\u6c42\u591a\u884c\u8f6c\u591a\u5217\u7ed3\u679c<\/h4>\n
\u95ee\u9898\u63cf\u8ff0<\/strong>\uff1a2014\u5e74\u516c\u53f8\u7ec4\u7ec7\u67b6\u6784\u8c03\u6574\uff0c\u5bfc\u81f4\u90e8\u95e8\u51fa\u73b0\u591a\u4e2a\u7ee9\u6548\uff0c\u4e1a\u52a1\u53ca\u4eba\u5458\u4e0d\u540c\uff0c\u65e0\u6cd5\u5408\u5e76\u7b97\u7ee9\u6548\uff0c\u6e90\u8868\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n
2014  B  9\n2015  A  8\n2014  A  10\n2015  B  7\n2014  B  6\n<\/code><\/pre>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
 a    col_A  col_B\n2014   10    6,9\n2015   8     7\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select \n    a,\n    max(case when b="A" then c end) col_A,\n    max(case when b="B" then c end) col_B\nfrom (\n    select \n        a,\n        b,\n        concat_ws(",",collect_set(cast(c as string))) as c\n    from t1\n    group by a,b\n)tmp\ngroup by a;\n<\/code><\/pre>\n
<\/span><\/a>\u4e8c\u3001\u6392\u540d\u4e2d\u53d6\u4ed6\u503c<\/h2>\n
\u8868\u540d<\/strong>\uff1at2<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a    b   c\n2014  A   3\n2014  B   1\n2014  C   2\n2015  A   4\n2015  D   3\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u6309a<\/strong>\u5206\u7ec4\u53d6b<\/strong>\u5b57\u6bb5\u6700\u5c0f\u65f6\u5bf9\u5e94\u7684c<\/strong>\u5b57\u6bb5<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a   min_c\n2014  3\n2015  4\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select\n  a,\n  c as min_c\nfrom\n(\n      select\n        a,\n        b,\n        c,\n        row_number() over(partition by a order by b) as rn \n      from t2 \n)a\nwhere rn = 1;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e8c\uff1a\u6309a<\/strong>\u5206\u7ec4\u53d6b<\/strong>\u5b57\u6bb5\u6392\u7b2c\u4e8c\u65f6\u5bf9\u5e94\u7684c<\/strong>\u5b57\u6bb5<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
 a  second_c\n2014  1\n2015  3\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select\n  a,\n  c as second_c\nfrom\n(\n      select\n        a,\n        b,\n        c,\n        row_number() over(partition by a order by b) as rn \n      from t2 \n)a\nwhere rn = 2;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e09\uff1a\u6309a<\/strong>\u5206\u7ec4\u53d6b<\/strong>\u5b57\u6bb5\u6700\u5c0f\u548c\u6700\u5927\u65f6\u5bf9\u5e94\u7684c<\/strong>\u5b57\u6bb5<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a    min_c  max_c\n2014  3      2\n2015  4      3\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select\n  a,\n  min(if(asc_rn = 1, c, null)) as min_c,\n  max(if(desc_rn = 1, c, null)) as max_c\nfrom\n(\n      select\n        a,\n        b,\n        c,\n        row_number() over(partition by a order by b) as asc_rn,\n        row_number() over(partition by a order by b desc) as desc_rn \n      from t2 \n)a\nwhere asc_rn = 1 or desc_rn = 1\ngroup by a; \n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u56db\uff1a\u6309a<\/strong>\u5206\u7ec4\u53d6b<\/strong>\u5b57\u6bb5\u7b2c\u4e8c\u5c0f\u548c\u7b2c\u4e8c\u5927\u65f6\u5bf9\u5e94\u7684c<\/strong>\u5b57\u6bb5<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a    min_c  max_c\n2014  1      1\n2015  3      4\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select\n    ret.a\n    ,max(case when ret.rn_min = 2 then ret.c else null end) as min_c\n    ,max(case when ret.rn_max = 2 then ret.c else null end) as max_c\nfrom (\n    select\n        *\n        ,row_number() over(partition by t2.a order by t2.b) as rn_min\n        ,row_number() over(partition by t2.a order by t2.b desc) as rn_max\n    from t2\n) as ret\nwhere ret.rn_min = 2\nor ret.rn_max = 2\ngroup by ret.a;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e94\uff1a\u6309a<\/strong>\u5206\u7ec4\u53d6b<\/strong>\u5b57\u6bb5\u524d\u4e24\u5c0f\u548c\u524d\u4e24\u5927\u65f6\u5bf9\u5e94\u7684c<\/strong>\u5b57\u6bb5<\/h4>\n
\u6ce8\u610f\uff1a\u9700\u4fdd\u6301b\u5b57\u6bb5\u6700\u5c0f\u3001\u6700\u5927\u6392\u9996\u4f4d<\/p>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a    min_c  max_c\n2014  3,1     2,1\n2015  4,3     3,4\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select\n  tmp1.a as a,\n  min_c,\n  max_c\nfrom \n(\n  select \n    a,\n    concat_ws(',', collect_list(c)) as min_c\n  from\n    (\n     select\n       a,\n       b,\n       c,\n       row_number() over(partition by a order by b) as asc_rn\n     from t2\n     )a\n    where asc_rn <= 2 \n    group by a \n)tmp1 \njoin \n(\n  select \n    a,\n    concat_ws(',', collect_list(c)) as max_c\n  from\n    (\n     select\n        a,\n        b,\n        c,\n        row_number() over(partition by a order by b desc) as desc_rn \n     from t2\n    )a\n    where desc_rn <= 2\n    group by a \n)tmp2 \non tmp1.a = tmp2.a; \n<\/code><\/pre>\n
<\/span><\/a>\u4e09\u3001\u7d2f\u8ba1\u6c42\u503c<\/h2>\n
\u8868\u540d<\/strong>\uff1at3<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a    b   c\n2014  A   3\n2014  B   1\n2014  C   2\n2015  A   4\n2015  D   3\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u6309a<\/strong>\u5206\u7ec4\u6309b<\/strong>\u5b57\u6bb5\u6392\u5e8f\uff0c\u5bf9c<\/strong>\u7d2f\u8ba1\u6c42\u548c<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a    b   sum_c\n2014  A   3\n2014  B   4\n2014  C   6\n2015  A   4\n2015  D   7\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n  a, \n  b, \n  c, \n  sum(c) over(partition by a order by b) as sum_c\nfrom t3; \n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e8c\uff1a\u6309a<\/strong>\u5206\u7ec4\u6309b<\/strong>\u5b57\u6bb5\u6392\u5e8f\uff0c\u5bf9c<\/strong>\u53d6\u7d2f\u8ba1\u5e73\u5747\u503c<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a    b   avg_c\n2014  A   3\n2014  B   2\n2014  C   2\n2015  A   4\n2015  D   3.5\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n  a, \n  b, \n  c, \n  avg(c) over(partition by a order by b) as avg_c\nfrom t3;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e09\uff1a\u6309a<\/strong>\u5206\u7ec4\u6309b<\/strong>\u5b57\u6bb5\u6392\u5e8f\uff0c\u5bf9b<\/strong>\u53d6\u7d2f\u8ba1\u6392\u540d\u6bd4\u4f8b<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a    b   ratio_c\n2014  A   0.33\n2014  B   0.67\n2014  C   1.00\n2015  A   0.50\n2015  D   1.00\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n  a, \n  b, \n  c, \n  round(row_number() over(partition by a order by b) \/ (count(c) over(partition by a)),2) as ratio_c\nfrom t3 \norder by a,b;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u56db\uff1a\u6309a<\/strong>\u5206\u7ec4\u6309b<\/strong>\u5b57\u6bb5\u6392\u5e8f\uff0c\u5bf9b<\/strong>\u53d6\u7d2f\u8ba1\u6c42\u548c\u6bd4\u4f8b<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a    b   ratio_c\n2014  A   0.50\n2014  B   0.67\n2014  C   1.00\n2015  A   0.57\n2015  D   1.00\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n  a, \n  b, \n  c, \n  round(sum(c) over(partition by a order by b) \/ (sum(c) over(partition by a)),2) as ratio_c\nfrom t3 \norder by a,b;\n<\/code><\/pre>\n
<\/span><\/a>\u56db\u3001\u7a97\u53e3\u5927\u5c0f\u63a7\u5236<\/h2>\n
\u8868\u540d<\/strong>\uff1at4<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a    b   c\n2014  A   3\n2014  B   1\n2014  C   2\n2015  A   4\n2015  D   3\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u6309a<\/strong>\u5206\u7ec4\u6309b<\/strong>\u5b57\u6bb5\u6392\u5e8f\uff0c\u5bf9c<\/strong>\u53d6\u524d\u540e\u5404\u4e00\u884c\u7684\u548c<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a    b   sum_c\n2014  A   1\n2014  B   5\n2014  C   1\n2015  A   3\n2015  D   4\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n  a,\n  b,\n  lag(c,1,0) over(partition by a order by b)+lead(c,1,0) over(partition by a order by b) as sum_c\nfrom t4;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e8c\uff1a\u6309a<\/strong>\u5206\u7ec4\u6309b<\/strong>\u5b57\u6bb5\u6392\u5e8f\uff0c\u5bf9c<\/strong>\u53d6\u5e73\u5747\u503c<\/h4>\n
\u95ee\u9898\u63cf\u8ff0<\/strong>\uff1a\u524d\u4e00\u884c\u4e0e\u5f53\u524d\u884c\u7684\u5747\u503c\uff01<\/p>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a    b   avg_c\n2014  A   3\n2014  B   2\n2014  C   1.5\n2015  A   4\n2015  D   3.5\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a
\n\u6b64\u5904\u7ed9\u51fa\u4e24\u79cd\u89e3\u6cd5\uff0c\u5176\u4e00\uff1a<\/p>\n
select\n    a,\n    b,\n    avg(c) over(partition by a order by b rows between 1 preceding and current row )\nfrom\nt4;\n<\/code><\/pre>\n
\u5176\u4e8c\uff1a<\/p>\n
select\n  a,\n  b,\n  case when lag_c is null then c\n  else (c+lag_c)\/2 end as avg_c\nfrom\n (\n select\n   a,\n   b,\n   c,\n   lag(c,1) over(partition by a order by b) as lag_c\n  from t4\n )temp;\n<\/code><\/pre>\n
<\/span><\/a>\u4e94\u3001\u4ea7\u751f\u8fde\u7eed\u6570\u503c<\/h2>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
...\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a
\n\u4e0d\u501f\u52a9\u5176\u4ed6\u4efb\u4f55\u5916\u8868\uff0c\u5b9e\u73b0\u4ea7\u751f\u8fde\u7eed\u6570\u503c<\/strong><\/em>
\n\u6b64\u5904\u7ed9\u51fa\u4e24\u79cd\u89e3\u6cd5\uff0c\u5176\u4e00\uff1a<\/p>\n
select\nid_start+pos as id\nfrom(\n    select\n    1 as id_start,\n    1000000 as id_end\n) m  lateral view posexplode(split(space(id_end-id_start), '')) t as pos, val\n<\/code><\/pre>\n
\u5176\u4e8c\uff1a<\/p>\n
select\n  row_number() over() as id\nfrom  \n  (select split(space(99), ' ') as x) t\nlateral view\nexplode(x) ex;\n<\/code><\/pre>\n
\u90a3\u5982\u4f55\u4ea7\u751f1\u81f31000000\u8fde\u7eed\u6570\u503c\uff1f<\/strong><\/p>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select\n  row_number() over() as id\nfrom  \n  (select split(space(999999), ' ') as x) t\nlateral view\nexplode(x) ex;\n<\/code><\/pre>\n
<\/span><\/a>\u516d\u3001\u6570\u636e\u6269\u5145\u4e0e\u6536\u7f29<\/h2>\n
\u8868\u540d<\/strong>\uff1at6<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u6570\u636e\u6269\u5145<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a     b\n3   3\u30012\u30011\n2   2\u30011\n4   4\u30013\u30012\u30011\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select  \n  t.a,\n  concat_ws('\u3001',collect_set(cast(t.rn as string))) as b\nfrom\n(  \n  select  \n    t6.a,\n    b.rn\n  from t6\n  left join\n  ( \n   select\n     row_number() over() as rn\n   from  \n   (select split(space(5), ' ') as x) t -- space(5)\u53ef\u6839\u636et6\u8868\u7684\u6700\u5927\u503c\u7075\u6d3b\u8c03\u6574\n   lateral view\n   explode(x) pe\n  ) b\n  on 1 = 1\n  where t6.a >= b.rn\n  order by t6.a, b.rn desc \n) t\ngroup by  t.a;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e8c\uff1a\u6570\u636e\u6269\u5145\uff0c\u6392\u9664\u5076\u6570<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a     b\n3   3\u30011\n2   1\n4   3\u30011\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select  \n  t.a,\n  concat_ws('\u3001',collect_set(cast(t.rn as string))) as b\nfrom\n(  \n  select  \n    t6.a,\n    b.rn\n  from t6\n  left join\n  ( \n   select\n     row_number() over() as rn\n   from  \n   (select split(space(5), ' ') as x) t\n   lateral view\n   explode(x) pe\n  ) b\n  on 1 = 1\n  where t6.a >= b.rn and b.rn % 2 = 1\n  order by t6.a, b.rn desc \n) t\ngroup by  t.a;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e09\uff1a\u5982\u4f55\u5904\u7406\u5b57\u7b26\u4e32\u7d2f\u8ba1\u62fc\u63a5<\/h4>\n
\u95ee\u9898\u63cf\u8ff0<\/strong>\uff1a\u5c06\u5c0f\u4e8e\u7b49\u4e8ea\u5b57\u6bb5\u7684\u503c\u805a\u5408\u62fc\u63a5\u8d77\u6765<\/p>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a     b\n3     2\u30013\n2     2\n4     2\u30013\u30014\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select  \n  t.a,\n  concat_ws('\u3001',collect_set(cast(t.a1 as string))) as b\nfrom\n(   \n  select  \n    t6.a,\n    b.a1\n  from t6\n  left join\n  (   \n   select  a as a1 \n   from t6\n  ) b\n  on 1 = 1\n  where t6.a >= b.a1\n  order by t6.a, b.a1 \n) t\ngroup by  t.a;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u56db\uff1a\u5982\u679ca\u5b57\u6bb5\u6709\u91cd\u590d\uff0c\u5982\u4f55\u5b9e\u73b0\u5b57\u7b26\u4e32\u7d2f\u8ba1\u62fc\u63a5<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a     b\n2     2\n3     2\u30013\n3     2\u30013\u30013\n4     2\u30013\u30013\u30014\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n  a,\n  b\nfrom \n(\n select  \n   t.a,\n   t.rn,\n   concat_ws('\u3001',collect_list(cast(t.a1 as string))) as b\n from\n  (   \n    select  \n     a.a,\n     a.rn,\n     b.a1\n    from\n    (\n     select  \n       a,\n       row_number() over(order by a ) as rn \n     from t6\n    ) a\n    left join\n    (   \n     select  a as a1,\n     row_number() over(order by a ) as rn  \n     from t6\n    ) b\n    on 1 = 1\n    where a.a >= b.a1 and a.rn >= b.rn \n    order by a.a, b.a1 \n  ) t\n  group by  t.a,t.rn\n  order by t.a,t.rn\n) tt; \n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e94\uff1a\u6570\u636e\u5c55\u5f00<\/h4>\n
\u95ee\u9898\u63cf\u8ff0<\/strong>\uff1a\u5982\u4f55\u5c06\u5b57\u7b26\u4e32"1-5,16,11-13,9"\u6269\u5c55\u6210"1,2,3,4,5,16,11,12,13,9"\uff1f\u6ce8\u610f\u987a\u5e8f\u4e0d\u53d8\u3002<\/p>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select  \n  concat_ws(',',collect_list(cast(rn as string)))\nfrom\n(\n  select  \n   a.rn,\n   b.num,\n   b.pos\n  from\n   (\n    select\n     row_number() over() as rn\n    from (select split(space(20), ' ') as x) t -- space(20)\u53ef\u7075\u6d3b\u8c03\u6574\n    lateral view\n    explode(x) pe\n   ) a lateral view outer \n   posexplode(split('1-5,16,11-13,9', ',')) b as pos, num\n   where a.rn between cast(split(num, '-')[0] as int) and cast(split(num, '-')[1] as int) or a.rn = num\n   order by pos, rn \n) t;\n<\/code><\/pre>\n
<\/span><\/a>\u4e03\u3001\u5408\u5e76\u4e0e\u62c6\u5206<\/h2>\n
\u8868\u540d<\/strong>\uff1at7<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a    b\n2014  A\n2014  B\n2015  B\n2015  D\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u5408\u5e76<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
2014  A\u3001B\n2015  B\u3001D\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select\n  a,\n  concat_ws('\u3001', collect_set(t.b)) b\nfrom t7\ngroup by a;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e8c\uff1a\u62c6\u5206<\/h4>\n
\u95ee\u9898\u63cf\u8ff0<\/strong>\uff1a\u5c06\u5206\u7ec4\u5408\u5e76\u7684\u7ed3\u679c\u62c6\u5206\u51fa\u6765<\/p>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select\n  t.a,\n  d\nfrom\n(\n select\n  a,\n  concat_ws('\u3001', collect_set(t7.b)) b\n from t7\n group by a\n)t\nlateral view \nexplode(split(t.b, '\u3001')) table_tmp as d;\n<\/code><\/pre>\n
<\/span><\/a>\u516b\u3001\u6a21\u62df\u5faa\u73af\u64cd\u4f5c<\/h2>\n
\u8868\u540d<\/strong>\uff1at8<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u5982\u4f55\u5c06\u5b57\u7b26’1’\u7684\u4f4d\u7f6e\u63d0\u53d6\u51fa\u6765<\/h5>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>:<\/p>\n
1,3,4\n2,4\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n    a,\n    concat_ws(",",collect_list(cast(index as string))) as res\nfrom (\n    select \n        a,\n        index+1 as index,\n        chr\n    from (\n        select \n            a,\n            concat_ws(",",substr(a,1,1),substr(a,2,1),substr(a,3,1),substr(a,-1)) str\n        from t8\n    ) tmp1\n    lateral view posexplode(split(str,",")) t as index,chr\n    where chr = "1"\n) tmp2\ngroup by a;\n<\/code><\/pre>\n
<\/span><\/a>\u4e5d\u3001\u4e0d\u4f7f\u7528distinct\u6216group by\u53bb\u91cd<\/h2>\n
\u8868\u540d<\/strong>\uff1at9<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a     b     c    d\n2014  2016  2014   A\n2014  2015  2015   B\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u4e0d\u4f7f\u7528distinct\u6216group by\u53bb\u91cd<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
2014  A\n2016  A\n2014  B\n2015  B\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select\n  t2.year\n  ,t2.num\nfrom\n (\n  select\n    *\n    ,row_number() over (partition by t1.year,t1.num) as rank_1\n  from \n  (\n    select \n      a as year,\n      d as num\n    from t9\n    union all\n    select \n      b as year,\n      d as num\n    from t9\n    union all\n    select \n      c as year,\n      d as num\n    from t9\n   )t1\n)t2\nwhere rank_1=1\norder by num;\n<\/code><\/pre>\n
<\/span><\/a>\u5341\u3001\u5bb9\u5668–\u53cd\u8f6c\u5185\u5bb9<\/h2>\n
\u8868\u540d<\/strong>\uff1at10<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a\nAB,CA,BAD\nBD,EA\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u53cd\u8f6c\u9017\u53f7\u5206\u9694\u7684\u6570\u636e\uff1a\u6539\u53d8\u987a\u5e8f\uff0c\u5185\u5bb9\u4e0d\u53d8<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
BAD,CA,AB\nEA,BD\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n  a,\n  concat_ws(",",collect_list(reverse(str)))\nfrom \n(\n  select \n    a,\n    str\n  from t10\n  lateral view explode(split(reverse(a),",")) t as str\n) tmp1\ngroup by a;\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e8c\uff1a\u53cd\u8f6c\u9017\u53f7\u5206\u9694\u7684\u6570\u636e\uff1a\u6539\u53d8\u5185\u5bb9\uff0c\u987a\u5e8f\u4e0d\u53d8<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
BA,AC,DAB\nDB,AE\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n  a,\n  concat_ws(",",collect_list(reverse(str)))\nfrom \n(\n  select \n     a,\n     str\n  from t10\n  lateral view explode(split(a,",")) t as str\n) tmp1\ngroup by a;\n<\/code><\/pre>\n
<\/span><\/a>\u5341\u4e00\u3001\u591a\u5bb9\u5668–\u6210\u5bf9\u63d0\u53d6\u6570\u636e<\/h2>\n
\u8868\u540d<\/strong>\uff1at11<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a       b\nA\/B     1\/3\nB\/C\/D   4\/5\/2\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u6210\u5bf9\u63d0\u53d6\u6570\u636e\uff0c\u5b57\u6bb5\u4e00\u4e00\u5bf9\u5e94<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a       b\nA       1\nB       3\nB       4\nC       5\nD       2\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select \n  a_inx,\n  b_inx\nfrom \n(\n  select \n     a,\n     b,\n     a_id,\n     a_inx,\n     b_id,\n     b_inx\n  from t11\n  lateral view posexplode(split(a,'\/')) t as a_id,a_inx\n  lateral view posexplode(split(b,'\/')) t as b_id,b_inx\n) tmp\nwhere a_id=b_id;\n<\/code><\/pre>\n
<\/span><\/a>\u5341\u4e8c\u3001\u591a\u5bb9\u5668–\u8f6c\u591a\u884c<\/h2>\n
\u8868\u540d<\/strong>\uff1at12<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a        b      c\n001     A\/B     1\/3\/5\n002     B\/C\/D   4\/5\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u8f6c\u591a\u884c<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a        d       e\n001     type_b    A\n001     type_b    B\n001     type_c    1\n001     type_c    3\n001     type_c    5\n002     type_b    B\n002     type_b    C\n002     type_b    D\n002     type_c    4\n002     type_c    5\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select \n  a,\n  d,\n  e\nfrom \n(\n  select\n    a,\n    "type_b" as d,\n    str as e\n  from t12\n  lateral view explode(split(b,"\/")) t as str\n  union all \n  select\n    a,\n    "type_c" as d,\n    str as e\n  from t12\n  lateral view explode(split(c,"\/")) t as str\n) tmp\norder by a,d;\n<\/code><\/pre>\n
<\/span><\/a>\u5341\u4e09\u3001\u62bd\u8c61\u5206\u7ec4–\u65ad\u70b9\u6392\u5e8f<\/h2>\n
\u8868\u540d<\/strong>\uff1at13<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a    b\n2014  1\n2015  1\n2016  1\n2017  0\n2018  0\n2019  -1\n2020  -1\n2021  -1\n2022  1\n2023  1\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u65ad\u70b9\u6392\u5e8f<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a    b    c \n2014  1    1\n2015  1    2\n2016  1    3\n2017  0    1\n2018  0    2\n2019  -1   1\n2020  -1   2\n2021  -1   3\n2022  1    1\n2023  1    2\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select  \n  a,\n  b,\n  row_number() over( partition by b,repair_a order by a asc) as c--\u6309\u7167b\u5217\u548c[b\u7684\u7ec4\u9996]\u5206\u7ec4\uff0c\u6392\u5e8f\nfrom \n(\n  select  \n    a,\n    b,\n    a-b_rn as repair_a--\u6839\u636eb\u5217\u503c\u51fa\u73b0\u7684\u6b21\u5e8f,\u4fee\u590da\u5217\u503c\u4e3ab\u9996\u6b21\u51fa\u73b0\u7684a\u5217\u503c,\u79f0\u4e3ab\u7684[\u7ec4\u9996]\n  from \n  (\n   select \n     a,\n     b,\n     row_number() over( partition by b order by  a  asc ) as b_rn--\u6309b\u5217\u5206\u7ec4,\u6309a\u5217\u6392\u5e8f,\u5f97\u5230b\u5217\u5404\u503c\u51fa\u73b0\u7684\u6b21\u5e8f\n   from t13 \n  )tmp1\n)tmp2--\u6ce8\u610f\uff0c\u5982\u679c\u4e0d\u540c\u7684b\u5217\u503c\uff0c\u53ef\u80fd\u51fa\u73b0\u540c\u6837\u7684\u7ec4\u9996\u503c\uff0c\u4f46\u7ec4\u9996\u503c\u9700\u8981\u548ca\u5217\u503c \u4e00\u5e76\u53c2\u4e0e\u5206\u7ec4\uff0c\u6545\u5e76\u4e0d\u5f71\u54cd\u6392\u5e8f\u3002\norder by a asc; \n<\/code><\/pre>\n
<\/span><\/a>\u5341\u56db\u3001\u4e1a\u52a1\u903b\u8f91\u7684\u5206\u7c7b\u4e0e\u62bd\u8c61–\u65f6\u6548<\/h2>\n
\u65e5\u671f\u8868<\/strong>\uff1ad_date<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
date_id      is_work\n2017-04-13       1\n2017-04-14       1\n2017-04-15       0\n2017-04-16       0\n2017-04-17       1\n<\/code><\/pre>\n
\u5de5\u4f5c\u65e5<\/strong><\/em>\uff1a\u5468\u4e00\u81f3\u5468\u4e9409:30-18:30<\/p>\n
\u5ba2\u6237\u7533\u8bf7\u8868<\/strong>\uff1at14<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a      b       c\n1     \u7533\u8bf7   2017-04-14 18:03:00\n1     \u901a\u8fc7   2017-04-17 09:43:00\n2     \u7533\u8bf7   2017-04-13 17:02:00\n2     \u901a\u8fc7   2017-04-15 09:42:00\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u8ba1\u7b97\u4e0a\u8868\u4e2d\u4ece\u7533\u8bf7\u5230\u901a\u8fc7\u5360\u7528\u7684\u5de5\u4f5c\u65f6\u957f<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a         d\n1        0.67h\n2       10.67h \n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select \n    a,\n    round(sum(diff)\/3600,2) as d\nfrom (\n    select \n        a,\n        apply_time,\n        pass_time,\n        dates,\n        rn,\n        ct,\n        is_work,\n        case when is_work=1 and rn=1 then unix_timestamp(concat(dates,' 18:30:00'),'yyyy-MM-dd HH:mm:ss')-unix_timestamp(apply_time,'yyyy-MM-dd HH:mm:ss')\n            when is_work=0 then 0\n            when is_work=1 and rn=ct then unix_timestamp(pass_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(concat(dates,' 09:30:00'),'yyyy-MM-dd HH:mm:ss')\n            when is_work=1 and rn!=ct then 9*3600\n        end diff\n    from (\n        select \n            a,\n            apply_time,\n            pass_time,\n            time_diff,\n            day_diff,\n            rn,\n            ct,\n            date_add(start,rn-1) dates\n        from (\n            select \n                a,\n                apply_time,\n                pass_time,\n                time_diff,\n                day_diff,\n                strs,\n                start,\n                row_number() over(partition by a) as rn,\n                count(*) over(partition by a) as ct\n            from (\n                select \n                    a,\n                    apply_time,\n                    pass_time,\n                    time_diff,\n                    day_diff,\n                    substr(repeat(concat(substr(apply_time,1,10),','),day_diff+1),1,11*(day_diff+1)-1) strs\n                from (\n                    select \n                        a,\n                        apply_time,\n                        pass_time,\n                        unix_timestamp(pass_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(apply_time,'yyyy-MM-dd HH:mm:ss') time_diff,\n                        datediff(substr(pass_time,1,10),substr(apply_time,1,10)) day_diff\n                    from (\n                        select \n                            a,\n                            max(case when b='\u7533\u8bf7' then c end) apply_time,\n                            max(case when b='\u901a\u8fc7' then c end) pass_time\n                        from t14\n                        group by a\n                    ) tmp1\n                ) tmp2\n            ) tmp3 \n            lateral view explode(split(strs,",")) t as start\n        ) tmp4\n    ) tmp5\n    join d_date \n    on tmp5.dates = d_date.date_id\n) tmp6\ngroup by a;\n<\/code><\/pre>\n
<\/span><\/a>\u5341\u4e94\u3001\u65f6\u95f4\u5e8f\u5217–\u8fdb\u5ea6\u53ca\u5269\u4f59<\/h2>\n
\u8868\u540d<\/strong>\uff1at15<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
date_id      is_work\n2017-07-30      0\n2017-07-31      1\n2017-08-01      1\n2017-08-02      1\n2017-08-03      1\n2017-08-04      1\n2017-08-05      0\n2017-08-06      0\n2017-08-07      1\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u6c42\u6bcf\u5929\u7684\u7d2f\u8ba1\u5468\u5de5\u4f5c\u65e5\uff0c\u5269\u4f59\u5468\u5de5\u4f5c\u65e5<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
date_id      week_to_work  week_left_work\n2017-07-31      1             4\n2017-08-01      2             3\n2017-08-02      3             2\n2017-08-03      4             1\n2017-08-04      5             0\n2017-08-05      5             0\n2017-08-06      5             0\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:
\n\u6b64\u5904\u7ed9\u51fa\u4e24\u79cd\u89e3\u6cd5\uff0c\u5176\u4e00\uff1a<\/p>\n
select \n date_id\n,case date_format(date_id,'u')\n    when 1 then 1\n    when 2 then 2 \n    when 3 then 3 \n    when 4 then 4\n    when 5 then 5 \n    when 6 then 5 \n    when 7 then 5 \n end as week_to_work\n,case date_format(date_id,'u')\n    when 1 then 4\n    when 2 then 3  \n    when 3 then 2 \n    when 4 then 1\n    when 5 then 0 \n    when 6 then 0 \n    when 7 then 0 \n end as week_to_work\nfrom t15\n<\/code><\/pre>\n
\u5176\u4e8c\uff1a<\/p>\n
select\ndate_id,\nweek_to_work,\nweek_sum_work-week_to_work as week_left_work\nfrom(\n    select\n    date_id,\n    sum(is_work) over(partition by year,week order by date_id) as week_to_work,\n    sum(is_work) over(partition by year,week) as week_sum_work\n    from(\n        select\n        date_id,\n        is_work,\n        year(date_id) as year,\n        weekofyear(date_id) as week\n        from t15\n    ) ta\n) tb order by date_id;\n<\/code><\/pre>\n
<\/span><\/a>\u5341\u516d\u3001\u65f6\u95f4\u5e8f\u5217–\u6784\u9020\u65e5\u671f<\/h2>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u76f4\u63a5\u4f7f\u7528SQL\u5b9e\u73b0\u4e00\u5f20\u65e5\u671f\u7ef4\u5ea6\u8868\uff0c\u5305\u542b\u4ee5\u4e0b\u5b57\u6bb5\uff1a<\/h4>\n
date                \tstring              \t\u65e5\u671f\nd_week              \tstring              \t\u5e74\u5185\u7b2c\u51e0\u5468\nweeks               \tint                 \t\u5468\u51e0\nw_start             \tstring              \t\u5468\u5f00\u59cb\u65e5\nw_end               \tstring              \t\u5468\u7ed3\u675f\u65e5\nd_month         \t   int                 \t\u7b2c\u51e0\u6708\nm_start         \t   string              \t\u6708\u5f00\u59cb\u65e5\nm_end           \t   string              \t\u6708\u7ed3\u675f\u65e5\nd_quarter            int                    \u7b2c\u51e0\u5b63\nq_start         \t   string              \t\u5b63\u5f00\u59cb\u65e5\nq_end           \t   string              \t\u5b63\u7ed3\u675f\u65e5\nd_year               int                    \u5e74\u4efd\ny_start         \t   string              \t\u5e74\u5f00\u59cb\u65e5\ny_end           \t   string              \t\u5e74\u7ed3\u675f\u65e5\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
drop table if exists dim_date;\ncreate table if not exists dim_date(\n    `date` string comment '\u65e5\u671f',\n    d_week string comment '\u5e74\u5185\u7b2c\u51e0\u5468',\n    weeks string comment '\u5468\u51e0',\n    w_start string comment '\u5468\u5f00\u59cb\u65e5',\n    w_end string comment '\u5468\u7ed3\u675f\u65e5',\n    d_month string comment '\u7b2c\u51e0\u6708',\n    m_start string comment '\u6708\u5f00\u59cb\u65e5',\n    m_end string comment '\u6708\u7ed3\u675f\u65e5',\n    d_quarter int comment '\u7b2c\u51e0\u5b63',\n    q_start string comment '\u5b63\u5f00\u59cb\u65e5',\n    q_end string comment '\u5b63\u7ed3\u675f\u65e5',\n    d_year int comment '\u5e74\u4efd',\n    y_start string comment '\u5e74\u5f00\u59cb\u65e5',\n    y_end string comment '\u5e74\u7ed3\u675f\u65e5'\n);\n--\u81ea\u7136\u6708: \u6307\u6bcf\u6708\u76841\u53f7\u5230\u90a3\u4e2a\u6708\u7684\u6708\u5e95\uff0c\u5b83\u662f\u6309\u7167\u9633\u5386\u6765\u8ba1\u7b97\u7684\u3002\u5c31\u662f\u4ece\u6bcf\u67081\u53f7\u5230\u6708\u5e95\uff0c\u4e0d\u7ba1\u8fd9\u4e2a\u6708\u670930\u5929\uff0c31\u5929\uff0c29\u5929\u6216\u800528\u5929\uff0c\u90fd\u7b97\u662f\u4e00\u4e2a\u81ea\u7136\u6708\u3002\n\ninsert overwrite table dim_date\nselect `date`\n     , d_week --\u5e74\u5185\u7b2c\u51e0\u5468\n     , case weekid\n           when 0 then '\u5468\u65e5'\n           when 1 then '\u5468\u4e00'\n           when 2 then '\u5468\u4e8c'\n           when 3 then '\u5468\u4e09'\n           when 4 then '\u5468\u56db'\n           when 5 then '\u5468\u4e94'\n           when 6 then '\u5468\u516d'\n    end  as weeks -- \u5468\n     , date_add(next_day(`date`,'MO'),-7) as w_start --\u5468\u4e00\n     , date_add(next_day(`date`,'MO'),-1) as w_end   -- \u5468\u65e5_end\n     -- \u6708\u4efd\u65e5\u671f\n     , concat('\u7b2c', monthid, '\u6708')  as d_month\n     , m_start\n     , m_end\n\n     -- \u5b63\u8282\n     , quarterid as d_quart\n     , concat(d_year, '-', substr(concat('0', (quarterid - 1) * 3 + 1), -2), '-01') as q_start --\u5b63\u5f00\u59cb\u65e5\n     , date_sub(concat(d_year, '-', substr(concat('0', (quarterid) * 3 + 1), -2), '-01'), 1) as q_end   --\u5b63\u7ed3\u675f\u65e5\n     -- \u5e74\n     , d_year\n     , y_start\n     , y_end\n\n\nfrom (\n         select `date`\n              , pmod(datediff(`date`, '2012-01-01'), 7)                  as weekid    --\u83b7\u53d6\u5468\u51e0\n              , cast(substr(`date`, 6, 2) as int)                        as monthid   --\u83b7\u53d6\u6708\u4efd\n              , case\n                    when cast(substr(`date`, 6, 2) as int) <= 3 then 1\n                    when cast(substr(`date`, 6, 2) as int) <= 6 then 2\n                    when cast(substr(`date`, 6, 2) as int) <= 9 then 3\n                    when cast(substr(`date`, 6, 2) as int) <= 12 then 4\n             end                                                       as quarterid --\u83b7\u53d6\u5b63\u8282 \u53ef\u4ee5\u76f4\u63a5\u4f7f\u7528 quarter(`date`)\n              , substr(`date`, 1, 4)                                     as d_year    -- \u83b7\u53d6\u5e74\u4efd\n              , trunc(`date`, 'YYYY')                                    as y_start   --\u5e74\u5f00\u59cb\u65e5\n              , date_sub(trunc(add_months(`date`, 12), 'YYYY'), 1) as y_end     --\u5e74\u7ed3\u675f\u65e5\n              , date_sub(`date`, dayofmonth(`date`) - 1)                 as m_start   --\u5f53\u6708\u7b2c\u4e00\u5929\n              , last_day(date_sub(`date`, dayofmonth(`date`) - 1))          m_end     --\u5f53\u6708\u6700\u540e\u4e00\u5929\n              , weekofyear(`date`)                                       as d_week    --\u5e74\u5185\u7b2c\u51e0\u5468\n         from (\n                    -- '2021-04-01'\u662f\u5f00\u59cb\u65e5\u671f, '2022-03-31'\u662f\u622a\u6b62\u65e5\u671f\n                  select date_add('2021-04-01', t0.pos) as `date`\n                  from (\n                           select posexplode(\n                                          split(\n                                                  repeat('o', datediff(\n                                                          from_unixtime(unix_timestamp('2022-03-31', 'yyyy-mm-dd'),\n                                                                        'yyyy-mm-dd'),\n                                                          '2021-04-01')), 'o'\n                                              )\n                                      )\n                       ) t0\n              ) t1\n     ) t2;\n<\/code><\/pre>\n
<\/span><\/a>\u5341\u4e03\u3001\u65f6\u95f4\u5e8f\u5217–\u6784\u9020\u7d2f\u79ef\u65e5\u671f<\/h2>\n
\u8868\u540d<\/strong>\uff1at17<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
date_id\n2017-08-01\n2017-08-02\n2017-08-03\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u6bcf\u4e00\u65e5\u671f\uff0c\u90fd\u6269\u5c55\u6210\u6708\u521d\u81f3\u5f53\u5929<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
date_id    date_to_day\n2017-08-01\t 2017-08-01\n2017-08-02\t 2017-08-01\n2017-08-02\t 2017-08-02\n2017-08-03\t 2017-08-01\n2017-08-03\t 2017-08-02\n2017-08-03\t 2017-08-03\n<\/code><\/pre>\n
\n
\u8fd9\u79cd\u7d2f\u79ef\u76f8\u5173\u7684\u8868\uff0c\u5e38\u505a\u6865\u63a5\u8868\u3002<\/p>\n<\/blockquote>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select\n  date_id,\n  date_add(date_start_id,pos) as date_to_day\nfrom\n(\n  select\n    date_id,\n    date_sub(date_id,dayofmonth(date_id)-1) as date_start_id\n  from t17\n) m  lateral view \nposexplode(split(space(datediff(from_unixtime(unix_timestamp(date_id,'yyyy-MM-dd')),from_unixtime(unix_timestamp(date_start_id,'yyyy-MM-dd')))), '')) t as pos, val;\n<\/code><\/pre>\n
<\/span><\/a>\u5341\u516b\u3001\u65f6\u95f4\u5e8f\u5217–\u6784\u9020\u8fde\u7eed\u65e5\u671f<\/h2>\n
\u8868\u540d<\/strong>\uff1at18<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
a             b         c\n101        2018-01-01     10\n101        2018-01-03     20\n101        2018-01-06     40\n102        2018-01-02     20\n102        2018-01-04     30\n102        2018-01-07     60\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u6784\u9020\u8fde\u7eed\u65e5\u671f<\/h4>\n
\u95ee\u9898\u63cf\u8ff0<\/strong>\uff1a\u5c06\u8868\u4e2d\u6570\u636e\u7684b\u5b57\u6bb5\u6269\u5145\u81f3\u8303\u56f4[2018-01-01, 2018-01-07]\uff0c\u5e76\u7d2f\u79ef\u5bf9c\u6c42\u548c\u3002
\nb\u5b57\u6bb5\u7684\u503c\u662f\u8f83\u7a00\u758f\u7684\u3002<\/p>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
a             b          c      d\n101        2018-01-01     10     10\n101        2018-01-02      0     10\n101        2018-01-03     20     30\n101        2018-01-04      0     30\n101        2018-01-05      0     30\n101        2018-01-06     40     70\n101        2018-01-07      0     70\n102        2018-01-01      0      0\n102        2018-01-02     20     20\n102        2018-01-03      0     20\n102        2018-01-04     30     50\n102        2018-01-05      0     50\n102        2018-01-06      0     50\n102        2018-01-07     60    110\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select\n  a,\n  b,\n  c,\n  sum(c) over(partition by a order by b) as d\nfrom\n(\n  select\n  t1.a,\n  t1.b,\n  case\n    when t18.b is not null then t18.c\n    else 0\n  end as c\n  from\n  (\n    select\n    a,\n    date_add(s,pos) as b\n    from\n    (\n      select\n        a, \n       '2018-01-01' as s, \n       '2018-01-07' as r\n      from (select a from t18 group by a) ta\n    ) m  lateral view \n      posexplode(split(space(datediff(from_unixtime(unix_timestamp(r,'yyyy-MM-dd')),from_unixtime(unix_timestamp(s,'yyyy-MM-dd')))), '')) t as pos, val\n  ) t1\n    left join t18\n    on  t1.a = t18.a and t1.b = t18.b\n) ts;\n<\/code><\/pre>\n
<\/span><\/a>\u5341\u4e5d\u3001\u65f6\u95f4\u5e8f\u5217–\u53d6\u591a\u4e2a\u5b57\u6bb5\u6700\u65b0\u7684\u503c<\/h2>\n
\u8868\u540d<\/strong>\uff1at19<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
date_id   a   b    c\n2014     AB  12    bc\n2015         23    \n2016               d\n2017     BC \n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u5982\u4f55\u4e00\u5e76\u53d6\u51fa\u6700\u65b0\u65e5\u671f<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
date_a   a    date_b    b    date_c   c\n2017    BC    2015     23    2016    d\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:
\n\u6b64\u5904\u7ed9\u51fa\u4e09\u79cd\u89e3\u6cd5\uff0c\u5176\u4e00\uff1a<\/p>\n
SELECT  max(CASE WHEN rn_a = 1 THEN date_id else 0 END) AS date_a\n        ,max(CASE WHEN rn_a = 1 THEN a else null END) AS a\n        ,max(CASE WHEN rn_b = 1 THEN date_id else 0 END) AS date_b\n        ,max(CASE WHEN rn_b = 1 THEN b else NULL  END) AS b\n        ,max(CASE WHEN rn_c = 1 THEN date_id  else 0 END) AS date_c\n        ,max(CASE WHEN rn_c = 1 THEN c else null END) AS c\nFROM    (\n            SELECT  date_id\n                    ,a\n                    ,b\n                    ,c\n                    --\u5bf9\u6bcf\u5217\u4e0a\u4e0d\u4e3anull\u7684\u503c  \u7684 \u65e5\u671f \u8fdb\u884c\u6392\u5e8f\n                    ,row_number()OVER( PARTITION BY 1 ORDER BY CASE WHEN a IS NULL THEN 0 ELSE date_id END DESC) AS rn_a\n                    ,row_number()OVER(PARTITION BY 1 ORDER BY CASE WHEN b IS NULL THEN 0 ELSE date_id END DESC) AS rn_b\n                    ,row_number()OVER(PARTITION BY 1 ORDER BY CASE WHEN c IS NULL THEN 0 ELSE date_id END DESC) AS rn_c\n            FROM    t19\n        ) t\nWHERE   t.rn_a = 1\nOR      t.rn_b = 1\nOR      t.rn_c = 1;\n<\/code><\/pre>\n
\u5176\u4e8c\uff1a<\/p>\n
SELECT  \n   a.date_id\n  ,a.a\n  ,b.date_id\n  ,b.b\n  ,c.date_id\n  ,c.c\nFROM\n(\n   SELECT  \n      t.date_id,\n      t.a\n   FROM  \n   (\n     SELECT  \n       t.date_id\n       ,t.a\n       ,t.b\n       ,t.c\n     FROM t19 t INNER JOIN    t19 t1 ON t.date_id = t1.date_id AND t.a IS NOT NULL\n   ) t\n   ORDER BY t.date_id DESC\n   LIMIT 1\n) a\nLEFT JOIN \n(\n  SELECT  \n    t.date_id\n    ,t.b\n  FROM    \n  (\n    SELECT  \n      t.date_id\n      ,t.b\n    FROM t19 t INNER JOIN t19 t1 ON t.date_id = t1.date_id AND t.b IS NOT NULL\n  ) t\n  ORDER BY t.date_id DESC\n  LIMIT 1\n) b ON 1 = 1 \nLEFT JOIN\n(\n  SELECT  \n    t.date_id\n    ,t.c\n  FROM    \n  (\n    SELECT  \n      t.date_id\n      ,t.c\n    FROM t19 t INNER JOIN t19 t1 ON t.date_id = t1.date_id AND t.c IS NOT NULL\n  ) t\n  ORDER BY t.date_id DESC\n  LIMIT   1\n) c\nON 1 = 1;\n<\/code><\/pre>\n
\u5176\u4e09\uff1a<\/p>\n
select \n  * \nfrom  \n(\n  select t1.date_id as date_a,t1.a from (select t1.date_id,t1.a  from t19 t1 where t1.a is not null) t1\n  inner join (select max(t1.date_id) as date_id   from t19 t1 where t1.a is not null) t2\n  on t1.date_id=t2.date_id\n) t1\ncross join\n(\n  select t1.date_b,t1.b from (select t1.date_id as date_b,t1.b  from t19 t1 where t1.b is not null) t1\n  inner join (select max(t1.date_id) as date_id   from t19 t1 where t1.b is not null)t2\n  on t1.date_b=t2.date_id\n) t2\ncross join \n(\n  select t1.date_c,t1.c from (select t1.date_id as date_c,t1.c  from t19 t1 where t1.c is not null) t1\n  inner join (select max(t1.date_id) as date_id   from t19 t1 where t1.c is not null)t2\n  on t1.date_c=t2.date_id\n) t3;\n<\/code><\/pre>\n
<\/span><\/a>\u4e8c\u5341\u3001\u65f6\u95f4\u5e8f\u5217–\u8865\u5168\u6570\u636e<\/h2>\n
\u8868\u540d<\/strong>\uff1at20<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
date_id   a   b    c\n2014     AB  12    bc\n2015         23    \n2016               d\n2017     BC \n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u5982\u4f55\u4f7f\u7528\u6700\u65b0\u6570\u636e\u8865\u5168\u8868\u683c<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
date_id   a   b    c\n2014     AB  12    bc\n2015     AB  23    bc\n2016     AB  23    d\n2017     BC  23    d\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:<\/p>\n
select \n  date_id, \n  first_value(a) over(partition by aa order by date_id) as a,\n  first_value(b) over(partition by bb order by date_id) as b,\n  first_value(c) over(partition by cc order by date_id) as c\nfrom\n(\n  select \n    date_id,\n    a,\n    b,\n    c,\n    count(a) over(order by date_id) as aa,\n    count(b) over(order by date_id) as bb,\n    count(c) over(order by date_id) as cc\n  from t20\n)tmp1;\n<\/code><\/pre>\n
<\/span><\/a>\u4e8c\u5341\u4e00\u3001\u65f6\u95f4\u5e8f\u5217–\u53d6\u6700\u65b0\u5b8c\u6210\u72b6\u6001\u7684\u524d\u4e00\u4e2a\u72b6\u6001<\/h2>\n
\u8868\u540d<\/strong>\uff1at21<\/code>
\n\u8868\u5b57\u6bb5\u53ca\u5185\u5bb9<\/strong>\uff1a<\/p>\n
date_id   a    b\n2014     1    A\n2015     1    B\n2016     1    A\n2017     1    B\n2013     2    A\n2014     2    B\n2015     2    A\n2014     3    A\n2015     3    A\n2016     3    B\n2017     3    A\n<\/code><\/pre>\n
\u4e0a\u8868\u4e2dB\u4e3a\u5b8c\u6210\u72b6\u6001<\/strong>\u3002<\/p>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u53d6\u6700\u65b0\u5b8c\u6210\u72b6\u6001\u7684\u524d\u4e00\u4e2a\u72b6\u6001<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
date_id  a    b\n2016     1    A\n2013     2    A\n2015     3    A\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>:
\n\u6b64\u5904\u7ed9\u51fa\u4e24\u79cd\u89e3\u6cd5\uff0c\u5176\u4e00\uff1a<\/p>\n
select\n    t21.date_id,\n    t21.a,\n    t21.b\nfrom\n    (\n        select\n            max(date_id) date_id,\n            a\n        from\n            t21\n        where\n            b = 'B'\n        group by\n            a\n    ) t1\n    inner join t21 on t1.date_id -1 = t21.date_id\nand t1.a = t21.a;\n<\/code><\/pre>\n
\u5176\u4e8c\uff1a<\/p>\n
select\n  next_date_id as date_id\n  ,a\n  ,next_b as b\nfrom(\n  select\n    *,min(nk) over(partition by a,b) as minb\n  from(\n    select\n      *,row_number() over(partition by a order by date_id desc) nk\n      ,lead(date_id) over(partition by a order by date_id desc) next_date_id\n      ,lead(b) over(partition by a order by date_id desc) next_b\n    from(\n      select * from t21\n    ) t\n  ) t\n) t\nwhere minb = nk and b = 'B';\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e8c\uff1a\u5982\u4f55\u5c06\u5b8c\u6210\u72b6\u6001\u7684\u8fc7\u7a0b\u5408\u5e76<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>:<\/p>\n
a   b_merge\n1   A\u3001B\u3001A\u3001B\n2   A\u3001B\n3   A\u3001A\u3001B\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select\n  a\n  ,collect_list(b) as b\nfrom(\n  select\n    *\n    ,min(if(b = 'B',nk,null)) over(partition by a) as minb\n  from(\n    select\n      *,row_number() over(partition by a order by date_id desc) nk\n    from(\n      select * from t21\n    ) t\n  ) t\n) t\nwhere nk >= minb\ngroup by a;\n<\/code><\/pre>\n
<\/span><\/a>\u4e8c\u5341\u4e8c\u3001\u975e\u7b49\u503c\u8fde\u63a5–\u8303\u56f4\u5339\u914d<\/h2>\n
\u8868f\u662f\u4e8b\u5b9e\u8868\uff0c\u8868d\u662f\u5339\u914d\u8868\uff0c\u5728hive\u4e2d\u5982\u4f55\u5c06\u5339\u914d\u8868\u4e2d\u7684\u503c\u5173\u8054\u5230\u4e8b\u5b9e\u8868\u4e2d\uff1f<\/p>\n
\u8868d\u76f8\u5f53\u4e8e\u62c9\u94fe\u8fc7\u7684\u53d8\u5316\u7ef4\uff0c\u4f46\u65e5\u671f\u8303\u56f4\u53ef\u80fd\u662f\u4e0d\u5168\u7684\u3002<\/p>\n
\u8868f<\/strong>\uff1a<\/p>\n
date_id  p_id\n 2017    C\n 2018    B\n 2019    A\n 2013    C\n<\/code><\/pre>\n
\u8868d<\/strong>\uff1a<\/p>\n
d_start    d_end    p_id   p_value\n 2016     2018     A       1\n 2016     2018     B       2\n 2008     2009     C       4\n 2010     2015     C       3\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u8303\u56f4\u5339\u914d<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
date_id  p_id   p_value\n 2017    C      null\n 2018    B      2\n 2019    A      null\n 2013    C      3\n<\/code><\/pre>\n
**\u53c2\u8003\u7b54\u6848<\/em>\uff1a
\n\u6b64\u5904\u7ed9\u51fa\u4e24\u79cd\u89e3\u6cd5\uff0c\u5176\u4e00\uff1a<\/p>\n
select \n  f.date_id,\n  f.p_id,\n  A.p_value\nfrom f \nleft join \n(\n  select \n    date_id,\n    p_id,\n    p_value\n  from \n  (\n    select \n      f.date_id,\n      f.p_id,\n      d.p_value\n    from f \n    left join d on f.p_id = d.p_id\n    where f.date_id >= d.d_start and f.date_id <= d.d_end\n  )A\n)A\nON f.date_id = A.date_id;\n<\/code><\/pre>\n
\u5176\u4e8c\uff1a<\/p>\n
select \n    date_id,\n    p_id,\n    flag as p_value\nfrom (\n    select \n        f.date_id,\n        f.p_id,\n        d.d_start,\n        d.d_end,\n        d.p_value,\n        if(f.date_id between d.d_start and d.d_end,d.p_value,null) flag,\n        max(d.d_end) over(partition by date_id) max_end\n    from f\n    left join d\n    on f.p_id = d.p_id\n) tmp\nwhere d_end = max_end;\n<\/code><\/pre>\n
<\/span><\/a>\u4e8c\u5341\u4e09\u3001\u975e\u7b49\u503c\u8fde\u63a5–\u6700\u8fd1\u5339\u914d<\/h2>\n
\n
\u8868t23_1\u548c\u8868t23_2\u901a\u8fc7a\u548cb\u5173\u8054\u65f6\uff0c\u6709\u76f8\u7b49\u7684\u53d6\u76f8\u7b49\u7684\u503c\u5339\u914d\uff0c\u4e0d\u76f8\u7b49\u65f6\u6bcf\u4e00\u4e2aa\u7684\u503c\u5728b\u4e2d\u627e\u5dee\u503c\u6700\u5c0f\u7684\u6765\u5339\u914d\u3002<\/p>\n<\/blockquote>\n
t23_1\u548ct23_2\u4e3a\u4e24\u4e2a\u73ed\u7684\u6210\u7ee9\u5355\uff0ct23_1\u73ed\u7684\u6bcf\u4e2a\u5b66\u751f\u6210\u7ee9\u5728t23_2\u73ed\u4e2d\u627e\u51fa\u6210\u7ee9\u6700\u63a5\u8fd1\u7684\u6210\u7ee9\u3002<\/p>\n
\u8868t23_1<\/strong>\uff1aa\u4e2d\u65e0\u91cd\u590d\u503c<\/p>\n
a\n<\/code><\/pre>\n
\u8868t23_2<\/strong>\uff1ab\u4e2d\u65e0\u91cd\u590d\u503c<\/p>\n
b\n<\/code><\/pre>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u5355\u5411\u6700\u8fd1\u5339\u914d<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a
\n\u6ce8\u610f\uff1ab\u7684\u503c\u53ef\u80fd\u4f1a\u88ab\u4e22\u5f03<\/p>\n
a    b\n1    2\n2    2\n4    3\n5    3\n5    7\n8    7\n10   11\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
select \n  * \nfrom\n(\n  select \n    ttt1.a,\n    ttt1.b \n  from\n  (\n    select \n      tt1.a,\n      t23_2.b,\n      dense_rank() over(partition by tt1.a order by abs(tt1.a-t23_2.b)) as dr \n    from \n    (\n      select \n        t23_1.a \n      from t23_1 \n      left join t23_2 on t23_1.a=t23_2.b \n      where t23_2.b is null\n    ) tt1 \n    cross join t23_2\n  ) ttt1 \n  where ttt1.dr=1 \n  union all\n  select \n    t23_1.a,\n    t23_2.b \n  from t23_1 \n  inner join t23_2 on t23_1.a=t23_2.b\n) result_t \norder by result_t.a;\n<\/code><\/pre>\n
<\/span><\/a>\u4e8c\u5341\u56db\u3001N\u6307\u6807–\u7d2f\u8ba1\u53bb\u91cd<\/h2>\n
\u5047\u8bbe\u8868A\u4e3a\u4e8b\u4ef6\u6d41\u6c34\u8868\uff0c\u5ba2\u6237\u5f53\u5929\u6709\u4e00\u6761\u8bb0\u5f55\u5219\u89c6\u4e3a\u5f53\u5929\u6d3b\u8dc3\u3002<\/p>\n
\u8868A<\/strong>\uff1a<\/p>\n
   time_id          user_id\n2018-01-01 10:00:00    001\n2018-01-01 11:03:00    002\n2018-01-01 13:18:00    001\n2018-01-02 08:34:00    004\n2018-01-02 10:08:00    002\n2018-01-02 10:40:00    003\n2018-01-02 14:21:00    002\n2018-01-02 15:39:00    004\n2018-01-03 08:34:00    005\n2018-01-03 10:08:00    003\n2018-01-03 10:40:00    001\n2018-01-03 14:21:00    005\n<\/code><\/pre>\n
\u5047\u8bbe\u5ba2\u6237\u6d3b\u8dc3\u975e\u5e38\uff0c\u4e00\u5929\u4ea7\u751f\u7684\u4e8b\u4ef6\u8bb0\u5f55\u5e73\u5747\u8fbe\u5343\u6761\u3002<\/p>\n
<\/span><\/a>\u95ee\u9898\u4e00\uff1a\u7d2f\u8ba1\u53bb\u91cd<\/h4>\n
\u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\u6240\u793a<\/strong>\uff1a<\/p>\n
\u65e5\u671f       \u5f53\u65e5\u6d3b\u8dc3\u4eba\u6570     \u6708\u7d2f\u8ba1\u6d3b\u8dc3\u4eba\u6570_\u622a\u81f3\u5f53\u65e5\ndate_id   user_cnt_act    user_cnt_act_month\n2018-01-01      2                2\n2018-01-02      3                4\n2018-01-03      3                5\n<\/code><\/pre>\n
\u53c2\u8003\u7b54\u6848<\/strong>\uff1a<\/p>\n
SELECT  tt1.date_id\n       ,tt2.user_cnt_act\n       ,tt1.user_cnt_act_month\nFROM\n(   -- \u2463 \u6309\u7167t.date_id\u5206\u7ec4\u6c42\u51fauser_cnt_act_month\uff0c\u5f97\u5230tt1\n\tSELECT  t.date_id\n\t       ,COUNT(user_id) AS user_cnt_act_month\n\tFROM\n\t(   -- \u2462 \u8868a\u548c\u8868b\u8fdb\u884c\u7b1b\u5361\u5c14\u79ef\uff0c\u6309\u7167a.date_id,b.user_id\u5206\u7ec4\uff0c\u4fdd\u8bc1\u622a\u6b62\u5230\u5f53\u65e5\u7684\u7528\u6237\u552f\u4e00\uff0c\u5f97\u51fa\u8868t\u3002\n\t\tSELECT  a.date_id\n\t\t       ,b.user_id\n\t\tFROM\n\t\t(   -- \u2460 \u6309\u7167\u65e5\u671f\u5206\u7ec4\uff0c\u53d6\u51fadate_id\u5b57\u6bb5\u5f53\u4e3b\u8868\u7684\u7ef4\u5ea6\u5b57\u6bb5 \u5f97\u51fa\u8868a\n\t\t\tSELECT  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id\n\t\t\tFROM test.temp_tanhaidi_20211213_1\n\t\t\tGROUP BY  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')\n\t\t) a\n\t\tINNER JOIN\n\t\t(   -- \u2461 \u6309\u7167date_id\u3001user_id\u5206\u7ec4\uff0c\u4fdd\u8bc1\u6bcf\u5929\u6bcf\u4e2a\u7528\u6237\u53ea\u6709\u4e00\u6761\u8bb0\u5f55\uff0c\u5f97\u51fa\u8868b\n\t\t\tSELECT  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id\n\t\t\t       ,user_id\n\t\t\tFROM test.temp_tanhaidi_20211213_1\n\t\t\tGROUP BY  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')\n\t\t\t         ,user_id\n\t\t) b\n\t\tON 1 = 1\n\t\tWHERE a.date_id >= b.date_id\n\t\tGROUP BY  a.date_id\n\t\t         ,b.user_id\n\t) t\n\tGROUP BY  t.date_id\n) tt1\nLEFT JOIN\n(   -- \u2465 \u6309\u7167date_id\u5206\u7ec4\u6c42\u51fauser_cnt_act\uff0c\u5f97\u5230tt2\n\tSELECT  date_id\n\t       ,COUNT(user_id) AS user_cnt_act\n\tFROM\n\t(   -- \u2464 \u6309\u7167\u65e5\u671f\u5206\u7ec4\uff0c\u53d6\u51fadate_id\u5b57\u6bb5\u5f53\u4e3b\u8868\u7684\u7ef4\u5ea6\u5b57\u6bb5 \u5f97\u51fa\u8868a\n\t\tSELECT  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id\n\t\t       ,user_id\n\t\tFROM test.temp_tanhaidi_20211213_1\n\t\tGROUP BY  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')\n\t\t         ,user_id\n\t) a\n\tGROUP BY date_id\n) tt2\nON tt2.date_id = tt1.date_id\n<\/code><\/pre>\n
\n\n
<\/div>","protected":false},"excerpt":{"rendered":"
\u4e00\u3001\u884c\u5217\u8f6c\u6362 \u63cf\u8ff0\uff1a\u8868\u4e2d\u8bb0\u5f55\u4e86\u5404\u5e74\u4efd\u5404\u90e8\u95e8\u7684\u5e73\u5747\u7ee9\u6548\u8003\u6838\u6210\u7ee9\u3002 \u8868\u540d\uff1at1 \u8868\u7ed3\u6784\uff1a a — \u5e74\u4efd b — \u90e8\u95e8 c — \u7ee9\u6548\u5f97\u5206 \u8868\u5185\u5bb9\uff1a a b c 2014 B 9 2015 A 8 2014 A 10 2015 B 7 \u95ee\u9898\u4e00\uff1a\u591a\u884c\u8f6c\u591a\u5217 \u95ee\u9898\u63cf\u8ff0\uff1a\u5c06\u4e0a\u8ff0\u8868\u5185\u5bb9\u8f6c\u4e3a\u5982\u4e0b\u8f93\u51fa\u7ed3 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0},"categories":[6],"tags":[],"_links":{"self":[{"href":"https:\/\/zhang.mba\/index.php\/wp-json\/wp\/v2\/posts\/1706"}],"collection":[{"href":"https:\/\/zhang.mba\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhang.mba\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhang.mba\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhang.mba\/index.php\/wp-json\/wp\/v2\/comments?post=1706"}],"version-history":[{"count":0,"href":"https:\/\/zhang.mba\/index.php\/wp-json\/wp\/v2\/posts\/1706\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhang.mba\/index.php\/wp-json\/wp\/v2\/media?parent=1706"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhang.mba\/index.php\/wp-json\/wp\/v2\/categories?post=1706"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhang.mba\/index.php\/wp-json\/wp\/v2\/tags?post=1706"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}