如何按concat\ws分组

ctrmrzij 于 2021-06-27 发布在 Hive

关注(0)|答案(5)|浏览(361)

我想跑 concat_ws 作用于 group by ，我得到下面的错误。意味着Hive不支持 concat_ws 进入 group by ? 如果没有，有没有其他的写作方法？
我在表中有以下记录（只有年、月和日）：

Year, Month, Date, 
2018, 08, 09 
2019, 09, 27
2017, 09, 27
2019, 02, 27
2019, 01, 27
2019, 01, 30
2019, 09, 27
2017, 09, 27
2019, 02, 27
2019, 01, 27
2019, 01, 30
..., ..., ...

有没有一种方法可以使用查询将我的记录分组到一些行中，从而将所有的年、月和日分组在一起？
查询的最终结果将是两行：

realdate,num
2019-01-27, 4
2019-01-28, 23
2019-01-29, 34
2019-02-01, 8
2019-02-02, 4

我认为查询应该是这样的：

select
        concat_ws('-', year, month, day) as realdate,count(*)
    from
        waterfall_table
    where
        concat_ws('-', year, month, day) between '2019-01-25' and '2019-02-10'
    group by  concat_ws('-', year, month, day)
    order by concat_ws('-', year, month, day) desc
    limit 100

sql Hive concatenation

来源：https://stackoverflow.com/questions/54665301/how-to-group-by-the-concat-ws

5条答案

按热度按时间

zzwlnbp81#

可以在GROUPBY和ORDERBY子句中使用列别名

SELECT CONCAT_WS('-', year, month, day) as realdate, COUNT(*)
FROM waterfall_table
WHERE CONCAT_WS('-', year, month, day) BETWEEN '2019-01-25' AND '2019-02-10'
GROUP BY realdate
ORDER BY realdate DESC
LIMIT 100

赞(0）回复(0）举报 2021-06-27

r8uurelv2#

请试试这个

select
  cast( ltrim( year) +'-'+ltrim( month) +'-'+ltrim( day ) as date),count(*)
from
    (select 2018 year ,10 month,25 day union select 2017 year ,11 month,10 
    union select 2016 year ,8 month,14 )t1
where
  cast( ltrim( year) +'-'+ltrim( month) +'-'+ltrim( day ) as date) between cast('2017-01-25' as date) and  cast('2019-02-10' as date)
 group by  year,month,day
order by  cast( ltrim( year) +'-'+ltrim( month) +'-'+ltrim( day ) as date) desc

赞(0）回复(0）举报 2021-06-27

vhmi4jdf3#

按使用别名排序：

with waterfall_table as
(
select stack(11,
'2018', '08', '09', 
'2019', '09', '27',
'2017', '09', '27',
'2019', '02', '27',
'2019', '01', '27',
'2019', '01', '30',
'2019', '09', '27',
'2017', '09', '27',
'2019', '02', '27',
'2019', '01', '27',
'2019', '01', '30') as (Year, Month, day)
)

select
        concat_ws('-', year, month, day) as realdate, count(*) as cnt
    from
        waterfall_table
    where
        concat_ws('-', year, month, day) between '2019-01-25' and '2019-02-10'
    group by  concat_ws('-', year, month, day)
    order by realdate desc
    limit 100;

结果：

OK
realdate        cnt
2019-01-30      2
2019-01-27      2
Time taken: 92.909 seconds, Fetched: 2 row(s)

这个jira似乎有关联：hive-1449

赞(0）回复(0）举报 2021-06-27

t2a7ltrp4#

hive对别名很挑剔。试试这个：
按realdate desc排序

赞(0）回复(0）举报 2021-06-27

jexiocij5#

如果我理解正确的话，你需要这样的东西：

SELECT CAST( CONCAT(`YEAR`, '-', `MONTH`, '-', `DATE` ) AS DATE ) AS realdate, COUNT(*) as num
FROM your_table
GROUP BY realdate

赞(0）回复(0）举报 2021-06-27

我来回答

如何按concat\ws分组

5条答案

相关问题

热门标签

最新问答