GreenPlum SQL转Hive SQL 含日期计算子查询等语法转换需求

1. 关联子查询语句改写 #

原转换困难的子查询片段逻辑为取上月与昨日（current_date-1）日号相同的日期，Hive可简化为 date_sub(date_sub(current_date(), 1), day(date_sub(current_date(), 1)))，无需复杂字符串截取操作。

select a.region, amount_fr, count_fr, amount_sr, count_sr 
from (
    select 
        region, 
        sum(cast(amount as int)) as amount_fr, 
        count(transid) as count_fr  
    from test.test_fr_imi
    where cast(trans_date as date) between add_months(trunc(date_sub(current_date(), 1), 'MM'), -1)
        and date_sub(date_sub(current_date(), 1), day(date_sub(current_date(), 1)))
    group by region
) a
join (
    select 
        region, 
        sum(cast(amount as int)) as amount_sr, 
        count(transid) as count_sr 
    from test.test_sr_imi
    where cast(trans_date as date) between add_months(trunc(date_sub(current_date(), 1), 'MM'), -1)
        and date_sub(date_sub(current_date(), 1), day(date_sub(current_date(), 1)))
    group by region
) b on a.region = b.region;

2. 其余3条查询语句改写 #

第1条 #

select format_number(round(sum(revenue)/1000000.00, 2), 0) as revenue_format
from test.sampletable 
where trxn_date = date_sub(current_date(), 1);

第2条 #

select format_number(round(
    sum(
        case 
            when date_format(trxn_date, 'yyyyMM') = date_format(add_months(date_sub(current_date(), 1), -1), 'yyyyMM') 
                and day(trxn_date) < day(current_date()) 
            then revenue 
            else 0 
        end
    )/1000000.00, 2), 0
) as revenue_format
from test.sample2table;

第3条 #

select concat(
    format_number(round(
        (
            (sum(case when date_format(trxn_date, 'yyyyMM') = date_format(date_sub(current_date(), 1), 'yyyyMM') then revenue else 0 end)
            - sum(case when date_format(trxn_date, 'yyyyMM') = date_format(add_months(date_sub(current_date(), 1), -1), 'yyyyMM') and day(trxn_date) < day(current_date()) then revenue else 0 end)
        ) / nullif(sum(case when date_format(trxn_date, 'yyyyMM') = date_format(add_months(date_sub(current_date(), 1), -1), 'yyyyMM') and day(trxn_date) < day(current_date()) then revenue else 0 end), 0)
        * 100.00, 2
    ), 2), '%'
) as revenue_rate
from test.sample3table;

内容的提问来源于stack exchange，提问作者Developer Rajinikanth