Hive相关

最新推荐文章于 2023-09-16 20:32:36 发布

御风之翼_唤星者

最新推荐文章于 2023-09-16 20:32:36 发布

阅读量125

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/xiaoxuonl/article/details/104753297

版权

MYSQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

--把多商品订单的支付金额拿出来
select 
explode(collect_set(split(split(payment,'":"')[1],'"')[0])) 
from (
select 
    payment
  from aaa.bbbbbb
  lateral view 
    explode(
      split(get_json_object(
            jdp_response
            ,"$.trade_fullinfo_get_response.trade.orders.order"),'payment')
      ) adTable as payment
WHERE   pt = 20200000
AND     tid = '11111111'
)

用符号分隔+拼接查询结果到同一行

SELECT  name
        ,concat_ws('|', collect_set(remark))
FROM    (
            SELECT  name,remark
            FROM    student
        ) 
GROUP BY name
;

--解析json里的子json

SELECT  b.traceid
        ,b.fusion
        ,b.param
        ,b.uri
FROM    (
            SELECT  explode(
                        split(
                            regexp_replace(
                                regexp_replace(
                                    SUBSTRING(actions,1,length(actions)-1)    --删掉最后一个]
                                    ,'\\[{'
                                    ,concat('{\"traceid\":\"',traceid,'\",')    --拼接traceid做关联
                                )
                                ,'},{'
                                ,concat('}split_sign{\"traceid\":\"',traceid,'\",')    --拼接traceid做关联
                            )
                            ,'split_sign'--自己设置的分隔符
                        )
                    ) AS DATA
            FROM    aaa
            WHERE   pt = ${bizdate}
        ) a
LATERAL VIEW json_tuple(a.DATA,'traceid','fusion','param','uri') b AS traceid,fusion,param,URI

【hive实现group_concat】

SELECT
	a.brand,
	COUNT(1) brand_count,
	concat_ws('|', collect_set(device)) device_brands,
	sysdate(- 1) dt
FROM
	(
		SELECT
			xxx_xxx [ 'brand' ] brand,
			xxx2 device
		FROM
			xx.xxx_log
		WHERE
			dt = sysdate(- 1)
		AND xx_id = 'xx2018_5131032'
	) a
GROUP BY
	a.brand