sql 求助，果然只会 mysql 的写 hive 要命

[Distand]

数据开发跑路了，只能自己去 hive 导数据，需求是：

1. xfrom='sugg'的全要

2. 其它 xfrom 的按 sub_region 分组根据 pubtime 一个月前取 xx 条，一个月内取 xx 条，都要按 click 倒序排

3. 汇总到一起

渣渣业务开发只能写出这种 sql 了，跑了下执行一次要 49 块钱，求个优化🙏

WITH total AS (
	SELECT * FROM xx.xx WHERE log_date='20220515'
),
other AS (
	SELECT * FROM total WHERE xfrom != 'sugg'
),
final AS (
  SELECT * FROM total WHERE xfrom='sugg' 
  UNION ALL
  SELECT * FROM (SELECT * FROM other WHERE sub_region='a' AND pubtime < '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 1000) a1
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='a' AND pubtime >= '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 1000) a2
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='b' AND pubtime < '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 1000) b1
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='b' AND pubtime >= '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 1000) b2
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='c' AND pubtime < '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 250) c1
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='c' AND pubtime >= '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 250) c2
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='d' AND pubtime < '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 500) d1
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='d' AND pubtime >= '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 500) d2
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='e' AND pubtime < '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 500) e1
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='e' AND pubtime >= '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 500) e2
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='f' AND pubtime < '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 50) f1
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='f' AND pubtime >= '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 50) f2
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='g' AND pubtime < '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 50) g1
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='g' AND pubtime >= '${yyyyMMdd,-1m}' ORDER BY click DESC LIMIT 50) g2
  UNION ALL 
  -- 这个 sub_region 有特殊要求，其它都是一样的条件，只是 limit 数量不同
  SELECT * FROM (SELECT * FROM other WHERE sub_region='h' AND pubtime < '${yyyyMMdd,-1m}' AND (tag LIKE '%th%' OR tag LIKE '%ph%') ORDER BY click DESC LIMIT 150) h1
  UNION ALL 
  SELECT * FROM (SELECT * FROM other WHERE sub_region='h' AND pubtime >= '${yyyyMMdd,-1m}' AND (tag LIKE '%th%' OR tag LIKE '%ph%') ORDER BY click DESC LIMIT 150) h2
)
INSERT OVERWRITE TABLE yy.yy SELECT * FROM final;

[efaun]

看到第二条说什么分组什么取多少条, 你看看分区函数适不适合你
https://www.cnblogs.com/linJie1930906722/p/6036053.html

[chineselittleboy]

最里面的 select * 改成想要的一些字段能便宜点

[zhusimaji]

关于取数分组的语句可以参考 select * from (select XXX, row_number() over (partition by sub_region order by click desc ) as rank from XXX)a where rank <1000

[zhusimaji]

所以后续一个月内和一个月外只要 union 一下就可以了，应该就可以输出结果了

[FYFX]

你下面那一堆 sql 可以根据 pubtime 分成两部分，然后用窗口函数 partition by sub_region order by click 算一下就行了吧，而且你这只取一天数据要 49 块钱是不是有点离谱

[lyang]

49 块钱是什么意思

[Distand]

感谢各位老哥，先 case when pubtime < '${yyyyMMdd,-1m}' then 0 else 1 end as pt ，再 row_number() over(partition by sub_region,pt order by click desc) as rank 后只需要 2 块钱了

[512357301]

这是一条 SQL 就搞定的吧(通过辅助列判断需要哪些数据，需要用到开窗函数，然后 where 就行了，怎么你这搞了那么多的 union all ，hive 执行 union all 效率很低的

[shuianfendi6]

用 partition 和 row_number 吧

spark-sql 执行效率会好不少