hive 记录帖待整理

最新推荐文章于 2025-09-11 19:58:31 发布

原创最新推荐文章于 2025-09-11 19:58:31 发布 · 346 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive

hive 专栏收录该内容

2 篇文章

订阅专栏

本文展示了一个使用Hive SQL进行复杂查询的例子，包括使用窗口函数row_number()进行行号分配，并结合子查询和条件过滤来精确获取所需数据。此外，还详细列出了查询计划，帮助理解Hive如何执行SQL语句。

select loanno,amount,limitpaydate,curplannum,
       row_number() over(PARTITION  by loanno ORDER by curplannum) as rn
 from ods.o_m20_cf_plan_s t
      where dt='2016-04-21'
      and substr(limitpaydate,1,10)>='2016-04-21'
      and loanno='2015112010543834'; 

2015112010543834    4500.0    2016-04-21 00:00:00    5    1
2015112010543834    4500.0    2016-05-21 00:00:00    6    2
2015112010543834    4500.0    2016-06-21 00:00:00    7    3
2015112010543834    4500.0    2016-10-21 00:00:00    11    4
2015112010543834    4500.0    2016-07-21 00:00:00    8    1
2015112010543834    4500.0    2016-08-21 00:00:00    9    2
2015112010543834    4500.0    2016-09-21 00:00:00    10    3


STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t
            filterExpr: ((substr(limitpaydate, 1, 10) >= '2016-04-21') and (loanno = '2015112010543834')) (type: boolean)
            Statistics: Num rows: 4792293 Data size: 10240206016 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: ((substr(limitpaydate, 1, 10) >= '2016-04-21') and (loanno = '2015112010543834')) (type: boolean)
              Statistics: Num rows: 798715 Data size: 1706699934 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: '2015112010543834' (type: string), curplannum (type: bigint)
                sort order: ++
                Statistics: Num rows: 798715 Data size: 1706699934 Basic stats: COMPLETE Column stats: NONE
                value expressions: '2015112010543834' (type: string), curplannum (type: bigint), limitpaydate (type: string), amount (type: double)
      Reduce Operator Tree:
        Extract
          Statistics: Num rows: 798715 Data size: 1706699934 Basic stats: COMPLETE Column stats: NONE
          PTF Operator
            Statistics: Num rows: 798715 Data size: 1706699934 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: '2015112010543834' (type: string), _col10 (type: double), _col8 (type: string), _col7 (type: bigint), _wcol0 (type: int)
              outputColumnNames: _col0, _col1, _col2, _col3, _col4
              Statistics: Num rows: 798715 Data size: 1706699934 Basic stats: COMPLETE Column stats: NONE
              File Output Operator
                compressed: false
                Statistics: Num rows: 798715 Data size: 1706699934 Basic stats: COMPLETE Column stats: NONE
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

select * from (
SELECT loanno 
  ,amount 
  ,limitpaydate 
  ,curplannum 
  ,row_number() over(partition BY loanno ORDER BY curplannum ASC) AS rn
FROM ods.o_m20_cf_plan_s                                             t
WHERE dt='2016-04-21'
    AND SUBSTR(limitpaydate,1,10)>='2016-04-21'
) t1 where loanno='2015112010543834'
2015112010543834    4500.0  2016-04-21 00:00:00 5   1
2015112010543834    4500.0  2016-05-21 00:00:00 6   2
2015112010543834    4500.0  2016-06-21 00:00:00 7   3
2015112010543834    4500.0  2016-07-21 00:00:00 8   4
2015112010543834    4500.0  2016-08-21 00:00:00 9   5
2015112010543834    4500.0  2016-09-21 00:00:00 10  6
2015112010543834    4500.0  2016-10-21 00:00:00 11  7

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t
            filterExpr: ((dt = '2016-04-21') and (substr(limitpaydate, 1, 10) >= '2016-04-21')) (type: boolean)
            Statistics: Num rows: 4792293 Data size: 10240206016 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (substr(limitpaydate, 1, 10) >= '2016-04-21') (type: boolean)
              Statistics: Num rows: 1597431 Data size: 3413402005 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: loanno (type: string), curplannum (type: bigint)
                sort order: ++
                Map-reduce partition columns: loanno (type: string)
                Statistics: Num rows: 1597431 Data size: 3413402005 Basic stats: COMPLETE Column stats: NONE
                value expressions: loanno (type: string), curplannum (type: bigint), limitpaydate (type: string), amount (type: double)
      Reduce Operator Tree:
        Extract
          Statistics: Num rows: 1597431 Data size: 3413402005 Basic stats: COMPLETE Column stats: NONE
          PTF Operator
            Statistics: Num rows: 1597431 Data size: 3413402005 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (_col6 = '2015112010543834') (type: boolean)
              Statistics: Num rows: 798715 Data size: 1706699934 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: '2015112010543834' (type: string), _col10 (type: double), _col8 (type: string), _col7 (type: bigint), _wcol0 (type: int)
                outputColumnNames: _col0, _col1, _col2, _col3, _col4
                Statistics: Num rows: 798715 Data size: 1706699934 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  Statistics: Num rows: 798715 Data size: 1706699934 Basic stats: COMPLETE Column stats: NONE
                  table:
                      input format: org.apache.hadoop.mapred.TextInputFormat
                      output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

hive 记录帖 待整理

hive 记录帖待整理