精华内容
下载资源
问答
  • Hive 日期函数 weekofyear

    千次阅读 2020-12-29 10:32:55
    日期转周函数:weekofyear 语法: weekofyear (string date) 返回值: int 说明:返回日期在当前的周数。 举例: hive> select weekofyear('2020-12-29') 53

    日期转周函数:weekofyear

    语法: weekofyear (string date)

    返回值: int

    说明:返回日期在当前的周数。

    举例:

    hive> select weekofyear('2020-12-29')

    53

    展开全文
  • hive weekofyear 怪异的姿势

    千次阅读 2017-07-19 19:58:56
    hive weekofyear : Error: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row

    hive weekofyear 怪异的姿势

    今天在使用hive函数weekofyear的时候遇到一个奇怪的情况,原sql如下:

    select aa.w,count(distinct aa.user_id),count(distinct bb.user_id)
    from
    (
    select weekofyear(date)w,user_id
    from dw.fct_ordr_pay
    where date>='2017-06-19' and date<='2017-07-09'
    and terminal_id in(5)
    group by weekofyear(date),user_id
    )aa 
    left join 
    (
    select weekofyear(date)w,user_id
    from dw.fct_ordr_pay
    where date>='2017-06-23' and date<='2017-07-16'
    and terminal_id in(5)
    group by weekofyear(date),user_id
    )bb on aa.user_id=bb.user_id and aa.w+1=bb.w
    group by aa.w
    order by aa.w
    
    

    执行过程中报错:

    Diagnostic Messages for this Task:
    Error: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row 
            at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.map(ExecMapper.java:179)
            at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:54)
            at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:453)
            at org.apache.hadoop.mapred.MapTask.run(MapTask.java:343)
            at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)
            at java.security.AccessController.doPrivileged(Native Method)
            at javax.security.auth.Subject.doAs(Subject.java:415)
            at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1693)
            at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
    Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row 
            at org.apache.hadoop.hive.ql.exec.vector.VectorMapOperator.process(VectorMapOperator.java:52)
            at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.map(ExecMapper.java:170)
            ... 8 more
    Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Error evaluating weekofyear(date)
            at org.apache.hadoop.hive.ql.exec.vector.VectorSelectOperator.processOp(VectorSelectOperator.java:126)
            at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815)
            at org.apache.hadoop.hive.ql.exec.vector.VectorFilterOperator.processOp(VectorFilterOperator.java:111)
            at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815)
            at org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:95)
            at org.apache.hadoop.hive.ql.exec.MapOperator$MapOpCtx.forward(MapOperator.java:157)
            at org.apache.hadoop.hive.ql.exec.vector.VectorMapOperator.process(VectorMapOperator.java:45)
            ... 9 more
    Caused by: java.lang.NullPointerException
            at java.nio.HeapByteBuffer.<init>(HeapByteBuffer.java:70)
            at java.nio.ByteBuffer.wrap(ByteBuffer.java:369)
            at org.apache.hadoop.io.Text.decode(Text.java:389)
            at org.apache.hadoop.hive.ql.exec.vector.expressions.VectorUDFWeekOfYearString.doGetField(VectorUDFWeekOfYearString.java:54)
            at org.apache.hadoop.hive.ql.exec.vector.expressions.VectorUDFTimestampFieldString.getField(VectorUDFTimestampFieldString.java:63)
            at org.apache.hadoop.hive.ql.exec.vector.expressions.VectorUDFTimestampFieldString.evaluate(VectorUDFTimestampFieldString.java:106)
            at org.apache.hadoop.hive.ql.exec.vector.VectorSelectOperator.processOp(VectorSelectOperator.java:124)
            ... 15 more
    

    分块执行:

    select weekofyear(date)w,user_id
    from dw.fct_ordr_pay
    where date>='2017-06-19' and date<='2017-07-09'
    and terminal_id in(5)
    group by weekofyear(date),user_id
    

    啊咧,WTF,同样的错误!!

    经过多次尝试,我发现只要where条件中加了terminal_id这个查询条件,就会报错。

    遂将原sql改为如下:

    select aa.w,aa.terminal_id,count(distinct aa.user_id),count(distinct bb.user_id)
    from
    (
    select weekofyear(date) as w,user_id,terminal_id
    from dw.fct_ordr_pay
    where date>='2017-06-19' and date<='2017-07-09'
    group by weekofyear(date),user_id,terminal_id
    )aa 
    left join 
    (
    select weekofyear(date)w,user_id,terminal_id
    from dw.fct_ordr_pay
    where date>='2017-06-23' and date<='2017-07-16'
    group by weekofyear(date),user_id,terminal_id
    )bb on aa.user_id=bb.user_id and aa.w+1=bb.w and aa.terminal_id=bb.terminal_id
    group by aa.w,aa.terminal_id
    order by aa.w,aa.terminal_id;
    

    Oh 老天,终于跑通了!

    经验:凡事,不要在一棵树上吊死,去傍边的树上多试试。关键的时候,换个姿势说不定更顺利。

    我的博客即将同步至腾讯云+社区,邀请大家一同入驻:https://cloud.tencent.com/developer/support-plan?invite_code=3ckgexxns8g0o

    展开全文
  • hive weekofyear 怪异的姿势 今天在使用hive函数weekofyear的时候遇到一个奇怪的情况,原sql如下: select aa.w,count(distinct aa.user_id),count(distinct bb.user_id) from ( select weekofyear(date)w,user_id ...

    hive weekofyear 怪异的姿势

    今天在使用hive函数weekofyear的时候遇到一个奇怪的情况,原sql如下:

    select aa.w,count(distinct aa.user_id),count(distinct bb.user_id)
    from
    (
    select weekofyear(date)w,user_id
    from dw.fct_ordr_pay
    where date>='2017-06-19' and date<='2017-07-09'
    and terminal_id in(5)
    group by weekofyear(date),user_id
    )aa 
    left join 
    (
    select weekofyear(date)w,user_id
    from dw.fct_ordr_pay
    where date>='2017-06-23' and date<='2017-07-16'
    and terminal_id in(5)
    group by weekofyear(date),user_id
    )bb on aa.user_id=bb.user_id and aa.w+1=bb.w
    group by aa.w
    order by aa.w
    

    执行过程中报错:

    Diagnostic Messages for this Task:
    Error: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row 
            at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.map(ExecMapper.java:179)
            at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:54)
            at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:453)
            at org.apache.hadoop.mapred.MapTask.run(MapTask.java:343)
            at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)
            at java.security.AccessController.doPrivileged(Native Method)
            at javax.security.auth.Subject.doAs(Subject.java:415)
            at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1693)
            at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
    Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row 
            at org.apache.hadoop.hive.ql.exec.vector.VectorMapOperator.process(VectorMapOperator.java:52)
            at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.map(ExecMapper.java:170)
            ... 8 more
    Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Error evaluating weekofyear(date)
            at org.apache.hadoop.hive.ql.exec.vector.VectorSelectOperator.processOp(VectorSelectOperator.java:126)
            at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815)
            at org.apache.hadoop.hive.ql.exec.vector.VectorFilterOperator.processOp(VectorFilterOperator.java:111)
            at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815)
            at org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:95)
            at org.apache.hadoop.hive.ql.exec.MapOperator$MapOpCtx.forward(MapOperator.java:157)
            at org.apache.hadoop.hive.ql.exec.vector.VectorMapOperator.process(VectorMapOperator.java:45)
            ... 9 more
    Caused by: java.lang.NullPointerException
            at java.nio.HeapByteBuffer.<init>(HeapByteBuffer.java:70)
            at java.nio.ByteBuffer.wrap(ByteBuffer.java:369)
            at org.apache.hadoop.io.Text.decode(Text.java:389)
            at org.apache.hadoop.hive.ql.exec.vector.expressions.VectorUDFWeekOfYearString.doGetField(VectorUDFWeekOfYearString.java:54)
            at org.apache.hadoop.hive.ql.exec.vector.expressions.VectorUDFTimestampFieldString.getField(VectorUDFTimestampFieldString.java:63)
            at org.apache.hadoop.hive.ql.exec.vector.expressions.VectorUDFTimestampFieldString.evaluate(VectorUDFTimestampFieldString.java:106)
            at org.apache.hadoop.hive.ql.exec.vector.VectorSelectOperator.processOp(VectorSelectOperator.java:124)
            ... 15 more

    分块执行:

    select weekofyear(date)w,user_id
    from dw.fct_ordr_pay
    where date>='2017-06-19' and date<='2017-07-09'
    and terminal_id in(5)
    group by weekofyear(date),user_id

    啊咧,WTF,同样的错误!!

    经过多次尝试,我发现只要where条件中加了terminal_id这个查询条件,就会报错。

    遂将原sql改为如下:

    select aa.w,aa.terminal_id,count(distinct aa.user_id),count(distinct bb.user_id)
    from
    (
    select weekofyear(date) as w,user_id,terminal_id
    from dw.fct_ordr_pay
    where date>='2017-06-19' and date<='2017-07-09'
    group by weekofyear(date),user_id,terminal_id
    )aa 
    left join 
    (
    select weekofyear(date)w,user_id,terminal_id
    from dw.fct_ordr_pay
    where date>='2017-06-23' and date<='2017-07-16'
    group by weekofyear(date),user_id,terminal_id
    )bb on aa.user_id=bb.user_id and aa.w+1=bb.w and aa.terminal_id=bb.terminal_id
    group by aa.w,aa.terminal_id
    order by aa.w,aa.terminal_id;

    yeah,终于跑通了!

    经验:凡事,不要在一棵树上吊死,去傍边的树上多试试。关键的时候,换个姿势说不定更顺利。

    转载于:https://www.cnblogs.com/myblog1900/p/10031885.html

    展开全文
  • mysql的yearweek 和 weekofyear函数

    千次阅读 2019-01-18 15:47:39
    1.MySQL 的 YEARWEEK 是获取年份和周数的一个函数,函数形式为 YEARWEEK(date[,mode]) 例如 2010-3-14 ,礼拜天 SELECT YEARWEEK('2010-3-14') 返回 11 SELECT YEARWEEK('2010-3-14',1...2. weekofyear函数是计算...

    1.MySQL 的 YEARWEEK 是获取年份和周数的一个函数,函数形式为 YEARWEEK(date[,mode])

    例如 2010-3-14 ,礼拜天

    SELECT YEARWEEK('2010-3-14') 返回 11
    SELECT YEARWEEK('2010-3-14',1) 返回 10

    其中第二个参数是 mode ,具体指的意思如下:

    2. weekofyear函数是计算出当前日期所在周数,和YEARWEEK('日期',1)的周数一致,但YEARWEEK('日期',1)在小于10的时候,不带0

    select WEEKOFYEAR('2016-2-2'); =5
    --------------------- 
    作者:技术人的突破 
    来源:CSDN 
    原文:https://blog.csdn.net/lifuxiangcaohui/article/details/50953018 
    版权声明:本文为博主原创文章,转载请附上博文链接!

    展开全文
  • JAVA的Calendar.WEEK_OF_YEAR与DB2或MySql数据库中week_iso(date)或weekOfYear(date),结果保持一致的方法 这样做统计的时候免去很多麻烦,无意中发现的,分享给大家!!!!!! 如果有高手,可以帮我解释一下,本人...
  • 1、日期转周函数:weekofyear 语法: weekofyear (string date) 返回值: int 说明:返回日期在当前的周数。 举例: hive> select weekofyear('2011-12-08 10:03:01') from lxw_dual; 2、LATERAL VIEW...
  • -- year_weeks select year_weeks ,substr(calendar_date_a,1,10) calendar_date from( select ceiling(dayofyear(calendar_date)/7) year_weeks,calendar_date_a from( select calendar_date,calendar_date ...
  •     SELECT YEARWEEK(now())#201904---不好用。比实际少了一周。大坑啊。 SELECT YEARWEEK(now(),1)#201905---用YEARWEEK...SELECT WEEKOFYEAR(now())#5 SELECT YEARWEEK('2019-02-02')#201904---YEARWEEK不好...
  • static   int  GetWeekOfYear() ... week  =  cal.GetWeekOfYear(DateTime.Now, cwr, dow);   return  week;  } 转载于:https://www.cnblogs.com/dflower/archive/2009/04/15/1436453.html
  • select to_date('2018-12-08 10:03:01');--2018-12-08 返回日期时间字段中的日期部分 select year('2018-12-08 10:03:01');--2018 select month('2018-12-08 10:...--1 select weekofyear('2018-12-08 10:03:01')--49
  • 用Sql server重做Delphi的weekoftheyear函数,有源码。
  • python代码根据时间获取周数(week of the year).pdf
  • System.out.println(sdf.format(cl.getTime())+"------"+cl.get(Calendar.WEEK_OF_YEAR)+"-------"+getWeekOfYear(cl)); cl.set(2015, 11, 31); // 使用SimpleDateFormat获取的周数是错误的,get(Calendar.WEEK_OF...
  • JAVA中calendar的week_of_year用法 注意操作前,请先getInstance week_of_year和两个值有关:firstDayOfWeek & minimalDaysInFirstWeek firstDayOfWeek: 一周的第一天周几开始,默认周日 //...
  • Calendar中的Calendar.WEEK_OF_YEAR

    千次阅读 2018-04-19 14:55:43
    原文转载自:https://blog.csdn.net/u010571844/article/details/495909071. 问题重现:当我们想使用Calendar.WEEK_OF_YEAR来求出2015-12-31属于2015年的第几周时,这里就会出现问题了。一年有52周,而2015-12-31这...
  • Calendar中的Calendar.WEEK_OF_YEAR陷阱

    万次阅读 2015-11-02 19:47:08
    问题重现:当我们想使用Calendar.WEEK_OF_YEAR来求出2015-12-31属于2015年的第几周时,这里就会出现问题了。一年有52周,而2015-12-31这一天是2015年的第53周,其实是2016年的第一周,所有这使用Calendar中的...
  • 在遇到很多关于时间得... 用calendar.get(Calendar.WEEK_OF_YEAR)获取到时当前星期在今年的星期数,但如果这个星期是跨年的,无论你当前时间是哪一年,calendar.get(Calendar.WEEK_OF_YEAR)得到的都会是1  如
  • MYSQL方法: select dt,if(month(dt)=1 and weekof...=52,year(dt)-1,year(dt)) as year,lpad(if(weekofyear(dt)= 11,weekofyear(dt)+52,weekofyear(dt)),2,'0') as week from n_match; JAVA方法 public Week...
  • 1. WEEK_OF_YEAR 一年中的第几周 由于西方的一周指的是:星期日-星期六,星期日是一周的第一天,星期六是一周的最后一天, 所以,使用 calendar.get(Calendar.WEEK_OF_YEAR) 时应该注意一周的开始应该是哪一天 ...
  • 西方的week概念, 周日是每周的开始, 周六是每周的结束. 但是中国的概念是周一是每周的开始 Calendar time = Calendar.getInstance(); time.setFirstDayOfWeek(2);//2是西方的星期一
  • Calendar cal = ... cal.set(Calendar.WEEK_OF_YEAR,18);//18为周数 Calendar cal1 = (Calendar)cal.clone(); int dayOfWeek = cal.get(Calendar.DAY_OF_WEEK); cal.add(Calendar.DATE,cal.getActualMin
  • int weeknum = c.get(Calendar.WEEK_OF_YEAR); 可是当有些日期如2010-10-1得到为1,但一般在日程中或安排计划中是划为2009年53周的,那要如何计算才能确保正确呢? 主要涉及两个参数setFirstDayOfWeek(),和...
  • impala之日期函数

    2020-09-19 16:02:53
    impala的weekofyear()是按照周一到周日为一个周来统计的。 select weekofyear('2020-01-01'); --第1周 周三 select weekofyear('2020-01-02'); select weekofyear('2020-01-03'); select weekofyear('2020-01-04'); ...
  • Determining Day of Week

    2008-10-22 23:42:47
    For the purpose of distinction, astronomical reckoning drops the symbols AD and BC and uses a plus or minus sign before the year instead. The astronomical year +1 therefore corresponds to the year AD...
  • 其中在计算周留存率时,可能会直接想到【weekofyear】这个函数,然后简单拼接年就可以得到周数,大部分时候是对的,但在跨年的时候可能会出现bug。 hive> select concat(year('2019-08-01'),'-',weekofyear('...
  • Day of Week

    千次阅读 2019-10-19 18:28:53
    We now use the Gregorian style of dating in Russia. The leap years are years with number divisible by 4 but not divisible by 100, or divisible by 400. For example, years 2004, 2180 and 2400 ar...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 52,239
精华内容 20,895
关键字:

WEEKOFYEAR