我要跟在蜂箱桌后面。列cycle_month
具有YYYYMM格式的值。
+---------------+--------------+------------+
| column_value | metric_name |cycle_month |
+---------------+--------------+------------+
| A37B | Mean | 202005 |
| ACCOUNT_ID | Mean | 202005 |
| ANB_200 | Mean | 202005 |
| ANB_201 | Mean | 202006 |
| AS82_RE | Mean | 202006 |
| ATTR001 | Mean | 202007 |
| ATTR001_RE | Mean | 202007 |
| ATTR002 | Mean | 202008 |
| ATTR002_RE | Mean | 202008 |
| ATTR003 | Mean | 202009 |
| ATTR004 | Mean | 202009 |
| ATTR005 | Mean | 202009 |
| ATTR006 | Mean | 202010 |
我需要编写一个动态查询,以获得用户传递的cycle_month值和cycle_month -4个月之间的值。
星火SQL查询:
select column_name, metric_name from table where cycle_month between add_months(to_date(202010,'YYYYMM'),-4) and 202010
获取误差
错误10015:第1行:323个参数长度错配''YYYYMM'':to_date()需要一个参数,got 2 (state=21000,code=10015)
预期产出:
+---------------+--------------+------------+
| column_value | metric_name |cycle_month |
+---------------+--------------+------------+
| ANB_201 | Mean | 202006 |
| AS82_RE | Mean | 202006 |
| ATTR001 | Mean | 202007 |
| ATTR001_RE | Mean | 202007 |
| ATTR002 | Mean | 202008 |
| ATTR002_RE | Mean | 202008 |
| ATTR003 | Mean | 202009 |
| ATTR004 | Mean | 202009 |
| ATTR005 | Mean | 202009 |
| ATTR006 | Mean | 202010 |
发布于 2020-11-28 07:22:03
Y
不是一年的正确格式;它应该是y
。您应该使用yyyyMM
。详情请参见https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html。
SELECT
column_name, metric_name, cycle_month
FROM
table
WHERE
to_date(cycle_month, 'yyyyMM') BETWEEN
add_months(to_date(202010, 'yyyyMM'), -4)
AND
to_date(202010, 'yyyyMM')
发布于 2020-11-28 02:48:28
函数期望字符串作为输入,并返回将日期更改为date_format
的date
,使用函数,最后将date
转换为int
.
Try with this query
select column_name, metric_name from table where cycle_month between int(date_format(add_months(to_date('202010','YYYYMM'),-4),'YYYYMM')) and 202010
UPDATE:
sql("select int(date_format(add_months(to_date('202010','YYYYMM'),-4),'YYYYMM'))").show()
#+------------------------------------------------------------------------------------------------+
#|CAST(date_format(CAST(add_months(to_date('202010', 'YYYYMM'), -4) AS TIMESTAMP), YYYYMM) AS INT)|
#+------------------------------------------------------------------------------------------------+
#| 201908|
#+------------------------------------------------------------------------------------------------+
https://stackoverflow.com/questions/65045494
复制相似问题