sparksql优化

m13020157417

已于 2024-08-16 11:05:45 修改

阅读量191

点赞数 10

文章标签： java 数据库算法

于 2024-08-15 20:41:18 首次发布

本文链接：https://blog.csdn.net/m13020157417/article/details/141231341

版权

SELECT a.sum_date
FROM
(SELECT '2024-08-06' AS sum_date
FROM
(SELECT ident_nbr_md5
FROM tmp_lcy.tablex3
WHERE day_id = '2024-08-06'
AND apply_dt >= '2020-01-01'
AND partner_nm != 'xxxx'
GROUP BY ident_nbr_md5 ) a ) a
LEFT JOIN
(SELECT '2024-08-06' AS sum_date
FROM tmp_lcy.tablex1
WHERE day_id >= '2024-01-01' ) c
ON a.sum_date = c.sum_date ;

CREATE TABLE `tmp_lcy`.`tablex3` (
`apply_dt` STRING ,
`partner_nm` STRING ,
`ident_nbr_md5` STRING ,
`day_id` STRING,
`prod_cd` STRING)
USING orc
PARTITIONED BY (day_id, prod_cd);

CREATE TABLE `tmp_lcy`.`tablex1` (
`mobile_nbr` STRING ,
`day_id` STRING ,
`org` STRING )
USING orc
PARTITIONED BY (day_id, org);

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SELECT day_id
        FROM tmp_lcy.tablex3
         a
LEFT JOIN
    (SELECT '2024-08-06' AS sum_date
    FROM tmp_lcy.tablex1
    WHERE day_id = '2024-01-01' ) c
    ON a.day_id = c.sum_date ;

SELECT a.day_id
        FROM tmp_lcy.tablex3
         a
LEFT JOIN
      tmp_lcy.tablex1
    c
    ON a.day_id = c.day_id ;

CREATE TABLE `tmp_lcy`.`tablex3` (
  `apply_dt` STRING    ,
  `partner_nm` STRING  ,
  `ident_nbr_md5` STRING    ,
  `day_id` STRING,
  `prod_cd` STRING)
USING orc
PARTITIONED BY (day_id, prod_cd);
 
CREATE TABLE `tmp_lcy`.`tablex1` (
  `mobile_nbr` STRING  ,
  `day_id` STRING  ,
  `org` STRING  )
USING orc
PARTITIONED BY (day_id, org);