with loan_apps as
(
SELECT
m.LA_LOAN_ID Loan_ID,
m.aid BORROWER_ACTOR_ID,
m.app_create_dt APPLICATION_DATE,
CASE WHEN m.PROD_ID in (1006 ,1008)
THEN 'TERM LOAN'
WHEN m.PROD_ID in(2000,2001,2002)
THEN 'LOC'
ELSE m.PROD_ID
END AS PRODUCT,
m.lc_loan_interest_rate as INTEREST_RATE,
m.lc_loan_apr as APR,
m.con_fico as FICO,
CC.FICO_MIN,
CC.FICO_MAX,
CR.A_INCOME,
CR.A_DOB,
CR.CR_DTI,
LA.ORIG_FEE_FRACTION as ORIG_FEE_FRACTION,
la.referer_id,
B.CITY_AREA as City_Area,
m.bus_zip as ZIP,
b.STATE as STATE,
b.dma as dma,
CASE WHEN m.loan_app_status = 0
THEN 'Internal'
WHEN m.loan_app_status = 1
THEN 'Pending'
WHEN m.loan_app_status = 2
THEN 'Failed Authentication'
WHEN m.loan_app_status = 3
THEN 'Failed Credit Rules'
WHEN m.loan_app_status = 4
THEN 'Passed Authentication'
WHEN m.loan_app_status = 5
THEN 'Passed Credit Rules'
WHEN m.loan_app_status = 6
THEN 'Submitted'
WHEN m.loan_app_status = 7
THEN 'Duplicate'
WHEN m.loan_app_status = 8
THEN 'Error'
WHEN m.loan_app_status = 9
THEN 'Approved'
WHEN m.loan_app_status = 10
THEN 'Rejected'
WHEN m.loan_app_status = 11
THEN 'In Review'
WHEN m.loan_app_status = 12
THEN 'Offer Expired'
ELSE 'Other'
END AS APP_STATUS,
m.l_pricing_grade as pricing_grade,
m.m_risk_grade as risk_grade, --this is the column that risk uses
m.lc_loan_maturity_months as LOAN_TERM,
m.channel_group_2 as channel_group,
m.channel_group_1 as partner_name,
CASE WHEN m.loan_app_status IN (6,9,10,11) THEN LA.app_d ELSE NULL END LISTING_DATE,
m.l_desired_amount as APPLICATION_AMOUNT,
CASE WHEN LA.LIST_D IS NOT NULL OR m.loan_app_status IN (6,9,10,11) THEN LA.APP_AMNT ELSE NULL END LISTING_AMOUNT,
L.APP_AMNT REDUCED_LISTING_AMOUNT,
m.loan_purpose as purpose,
case when m.loan_registered = 1 then 1 else 0 end as REGISTERED, --flag that shows 0/1 for loan registered
CASE WHEN m.FI_PROVIDED = 1 AND m.RESPONSE NOT IN ('MOREINFO_NEEDED','TECHNICAL_ERROR') THEN 1 ELSE 0 END AS COMPLETED_APPS,
case when m.LOAN_OFFERED = 1 then 1 else 0 end AS LOANS_OFFERED,
case when m.LOAN_LISTED = 1 then 1 else 0 end AS LOANS_LISTED,
case when m.all_core_docs_received = 1 then 1 else 0 end as all_core_docs_received_flag,
case when m.FRONTEND_DECLINE = 1 then 1 else 0 end as frontend_decline_flag,
case when m.backend_decline = 1 then 1 else 0 end as backend_decline_flag,
case when m.LOAN_ISSUED = 1 then 1 else 0 end AS LOANS_ISSUED,
case when m.issued_loan_strt_dt is not null then m.issued_loan_strt_dt else null end as ISSUED_DATE,
CASE WHEN m.issued_loan_strt_dt IS NOT NULL THEN m.l_funded_amnt ELSE NULL END ISSUED_AMOUNT,
CASE WHEN m.loan_status = 1
THEN 'In-Funding'
WHEN m.loan_status = 4
THEN 'Issued'
WHEN m.loan_status = 6
THEN 'Paid Off'
WHEN m.loan_status = 10
THEN 'Current'
WHEN m.loan_status = 11
THEN 'In Grace Period'
WHEN m.loan_status = 12
THEN 'Late 16-30 Days'
WHEN m.loan_status = 13
THEN 'Late 31+ Days'
WHEN m.loan_status = 14
THEN 'Default'
WHEN m.loan_status = 15
THEN 'Charged Off'
WHEN m.loan_status = 2
THEN 'Expired'
WHEN m.loan_status = 3
THEN 'Declined'
ELSE 'Not Issued'
END AS LOAN_STATUS,
CASE WHEN B.CITY_AREA IN ('HOUSTON','DENVER','MIAMI-FT. LAUDERDALE')
THEN 'TEST'
WHEN B.CITY_AREA IN ('NEW YORK','LOS ANGELES','SAN FRANCISCO-OAK-SAN JOSE','DALLAS-FT. WORTH','SAN DIEGO')
THEN 'CONTROL'
ELSE
'OTHER'
END AS TEST_OR_CONTROL,
case when m.loan_registered=1 and m.pi_provided=0 then 'registered'
when m.pi_provided=1 and m.fi_provided=0 then 'pi_provided'
when m.fi_provided=1 and (case when m.PB_FLAG in ('PB DECLINE','PB APPROVED') then 1 else 0 end)=0 then 'fi_provided'
when (case when m.PB_FLAG in ('PB DECLINE','PB APPROVED') then 1 else 0 end)=1 and m.loan_offered=0 then 'app_completed'
when m.loan_offered=1 and (CASE WHEN la.sel_offer_item_id IS NOT NULL THEN 1 ELSE 0 END)=0 then 'offered'
when (CASE WHEN la.sel_offer_item_id IS NOT NULL THEN 1 ELSE 0 END)=1 and (CASE WHEN bi.business_tax_id_enc IS NOT NULL THEN 1 ELSE 0 END)=0 then 'offer selected'
when (CASE WHEN bi.business_tax_id_enc IS NOT NULL THEN 1 ELSE 0 END)=1 and m.loan_listed=0 then 'checked out'
when m.loan_listed=1 and (m.all_core_docs_received=0 and m.loan_issued=0) then 'listed'
when m.all_core_docs_received=1 and m.loan_issued=0 then 'docs uploaded'
when m.loan_issued=1 then 'issued or approved'
else null
end as funnel_action,
m.ver_b_revenue as revenue_last_yr,
m.u_inpt_net_profit as profit_last_yr,
m.num_of_employees_group_1 as head_count,
m.m_legal_structure as legal_structure,
m.years_in_business as years_in_business,
MKTC.name as campaign_name,
MKTC.id as campaign_id
FROM
MARKETING.SB_LOANS_DATA_W_PERF_GRP m
left join TLC.LC_LOAN_APP LA ON M.la_loan_id=la.loan_id
left join TLC.LC_actor a on a.id=la.aid
LEFT JOIN marketing_temp.tv_ad_control_zipcode b ON m.bus_zip = b.zip_code
left jOIN TLC.LC_business_info bi ON bi.id = la.business_info_id
left join TLC.LC_LOAN L on l.id=la.LOAN_ID
left join TLC.LC_CREDIT_CLASS CC on cc.id=la.CREDIT_CLASS_ID
left join TLC.LC_CREDIT CR on cr.id=la.CR_ID
left join TLC.LC_MKT_CAMPAIGN MKTC ON la.REFERER_ID = MKTC.OLD_CAMPAIGN_ID
)
select
Loan_ID,
BORROWER_ACTOR_ID,
purpose,
'REGISTERED' as action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
null as LISTING_DATE,
null as LISTING_AMOUNT,
null as ISSUED_DATE,
null as ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
from loan_apps
where REGISTERED = 1
union all
select Loan_ID,
BORROWER_ACTOR_ID,
purpose,
'COMPLETED_APPS' as action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
null as LISTING_DATE,
null as LISTING_AMOUNT,
null as ISSUED_DATE,
null as ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
from loan_apps
where COMPLETED_APPS = 1
union all
select Loan_ID,
BORROWER_ACTOR_ID,
purpose,
'LOANS_OFFERED' as action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
null as LISTING_DATE,
null as LISTING_AMOUNT,
null as ISSUED_DATE,
null as ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
from loan_apps
where LOANS_OFFERED = 1
union all
select Loan_ID,
BORROWER_ACTOR_ID,
purpose,
'LOANS_LISTED' as action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
LISTING_DATE,
LISTING_AMOUNT,
null as ISSUED_DATE,
null as ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
from loan_apps
where LOANS_LISTED = 1
union all
select Loan_ID,
BORROWER_ACTOR_ID,
purpose,
'all_core_docs_received' as action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
null as LISTING_DATE,
null as LISTING_AMOUNT,
null as ISSUED_DATE,
null as ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
from loan_apps
where all_core_docs_received_flag = 1
union all
select Loan_ID,
BORROWER_ACTOR_ID,
purpose,
'frontend_decline' as action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
null as LISTING_DATE,
null as LISTING_AMOUNT,
null as ISSUED_DATE,
null as ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
from loan_apps
where frontend_decline_flag = 1
union all
select Loan_ID,
BORROWER_ACTOR_ID,
purpose,
'backend_decline_flag' as action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
null as LISTING_DATE,
null as LISTING_AMOUNT,
null as ISSUED_DATE,
null as ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
from loan_apps
where backend_decline_flag = 1
union all
select Loan_ID,
BORROWER_ACTOR_ID,
purpose,
'LOANS_ISSUED' as action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
null as LISTING_DATE,
null as LISTING_AMOUNT,
ISSUED_DATE,
ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
from loan_apps
where LOANS_ISSUED = 1
这是我尝试执行Tableau中的代码时收到的错误消息:[Hortonworks] [HiveODBC](35)来自Hive的错误:错误代码:'40000'错误消息:'编译语句时发生错误:FAILED:ParseException行143 :2无法识别谓词“选择”。失败的规则:子查询源中的“queryStatementExpression”。
最佳答案
Tableau将整个查询包装在子查询中，并且Hive不支持在子查询中使用WITH ..，这是在Tableau中将其作为自定义SQL编写时此查询失败的原因。解决方案是创建一个 View ，然后在Tableau中的自定义SQL中引用该 View 。
create view marketing_temp.apps
AS
SELECT m.LA_LOAN_ID ID,
m.aid BORROWER_ACTOR_ID,
m.app_create_dt APPLICATION_DATE,
CASE
WHEN m.PROD_ID IN (1006,1008) THEN 'TERM LOAN'
WHEN m.PROD_ID IN (2000,2001,2002) THEN 'LOC'
ELSE m.PROD_ID
END AS PRODUCT,
m.lc_loan_interest_rate AS INTEREST_RATE,
m.lc_loan_apr AS APR,
m.con_fico AS FICO,
CC.FICO_MIN,
CC.FICO_MAX,
CR.A_INCOME,
CR.A_DOB,
CR.CR_DTI,
LA.ORIG_FEE_FRACTION AS ORIG_FEE_FRACTION,
la.referer_id,
B.CITY_AREA AS City_Area,
m.bus_zip AS ZIP,
b.STATE AS STATE,
b.dma AS dma,
CASE
WHEN m.loan_app_status = 0 THEN 'Internal'
WHEN m.loan_app_status = 1 THEN 'Pending'
WHEN m.loan_app_status = 2 THEN 'Failed Authentication'
WHEN m.loan_app_status = 3 THEN 'Failed Credit Rules'
WHEN m.loan_app_status = 4 THEN 'Passed Authentication'
WHEN m.loan_app_status = 5 THEN 'Passed Credit Rules'
WHEN m.loan_app_status = 6 THEN 'Submitted'
WHEN m.loan_app_status = 7 THEN 'Duplicate'
WHEN m.loan_app_status = 8 THEN 'Error'
WHEN m.loan_app_status = 9 THEN 'Approved'
WHEN m.loan_app_status = 10 THEN 'Rejected'
WHEN m.loan_app_status = 11 THEN 'In Review'
WHEN m.loan_app_status = 12 THEN 'Offer Expired'
ELSE 'Other'
END AS APP_STATUS,
m.l_pricing_grade AS pricing_grade,
m.m_risk_grade AS risk_grade,
--this is the column that risk uses
m.lc_loan_maturity_months AS LOAN_TERM,
m.channel_group_2 AS channel_group,
m.channel_group_1 AS partner_name,
CASE
WHEN m.loan_app_status IN (6,9,10,11) THEN LA.app_d
ELSE NULL
END LISTING_DATE,
m.l_desired_amount AS APPLICATION_AMOUNT,
CASE
WHEN LA.LIST_D IS NOT NULL OR m.loan_app_status IN (6,9,10,11) THEN LA.APP_AMNT
ELSE NULL
END LISTING_AMOUNT,
L.APP_AMNT REDUCED_LISTING_AMOUNT,
m.loan_purpose AS purpose,
CASE
WHEN m.loan_registered = 1 THEN 1
ELSE 0
END AS REGISTERED,
--flag that shows 0/1 for loan registered
CASE
WHEN m.FI_PROVIDED = 1 AND m.RESPONSE NOT IN ('MOREINFO_NEEDED','TECHNICAL_ERROR') THEN 1
ELSE 0
END AS COMPLETED_APPS,
CASE
WHEN m.LOAN_OFFERED = 1 THEN 1
ELSE 0
END AS LOANS_OFFERED,
CASE
WHEN m.LOAN_LISTED = 1 THEN 1
ELSE 0
END AS LOANS_LISTED,
CASE
WHEN m.all_core_docs_received = 1 THEN 1
ELSE 0
END AS all_core_docs_received_flag,
CASE
WHEN m.FRONTEND_DECLINE = 1 THEN 1
ELSE 0
END AS frontend_decline_flag,
CASE
WHEN m.backend_decline = 1 THEN 1
ELSE 0
END AS backend_decline_flag,
CASE
WHEN m.LOAN_ISSUED = 1 THEN 1
ELSE 0
END AS LOANS_ISSUED,
CASE
WHEN m.issued_loan_strt_dt IS NOT NULL THEN m.issued_loan_strt_dt
ELSE NULL
END AS ISSUED_DATE,
CASE
WHEN m.issued_loan_strt_dt IS NOT NULL THEN m.l_funded_amnt
ELSE NULL
END ISSUED_AMOUNT,
CASE
WHEN m.loan_status = 1 THEN 'In-Funding'
WHEN m.loan_status = 4 THEN 'Issued'
WHEN m.loan_status = 6 THEN 'Paid Off'
WHEN m.loan_status = 10 THEN 'Current'
WHEN m.loan_status = 11 THEN 'In Grace Period'
WHEN m.loan_status = 12 THEN 'Late 16-30 Days'
WHEN m.loan_status = 13 THEN 'Late 31+ Days'
WHEN m.loan_status = 14 THEN 'Default'
WHEN m.loan_status = 15 THEN 'Charged Off'
WHEN m.loan_status = 2 THEN 'Expired'
WHEN m.loan_status = 3 THEN 'Declined'
ELSE 'Not Issued'
END AS LOAN_STATUS,
CASE
WHEN B.CITY_AREA IN ('HOUSTON','DENVER','MIAMI-FT. LAUDERDALE') THEN 'TEST'
WHEN B.CITY_AREA IN ('NEW YORK','LOS ANGELES','SAN FRANCISCO-OAK-SAN JOSE','DALLAS-FT. WORTH','SAN DIEGO') THEN 'CONTROL'
ELSE 'OTHER'
END AS TEST_OR_CONTROL,
CASE
WHEN m.loan_registered = 1 AND m.pi_provided = 0 THEN 'registered'
WHEN m.pi_provided = 1 AND m.fi_provided = 0 THEN 'pi_provided'
WHEN m.fi_provided = 1 AND (CASE
WHEN m.PB_FLAG IN ('PB DECLINE','PB APPROVED') THEN 1
ELSE 0
END) = 0 THEN 'fi_provided' WHEN (CASE WHEN m.PB_FLAG IN ('PB DECLINE','PB APPROVED') THEN 1 ELSE 0 END) = 1 AND m.loan_offered = 0 THEN 'app_completed' WHEN m.loan_offered = 1 AND (CASE WHEN la.sel_offer_item_id IS NOT NULL THEN 1 ELSE 0 END) = 0 THEN 'offered' WHEN (CASE WHEN la.sel_offer_item_id IS NOT NULL THEN 1 ELSE 0 END) = 1 AND (CASE WHEN bi.business_tax_id_enc IS NOT NULL THEN 1 ELSE 0 END) = 0 THEN 'offer selected' WHEN (CASE WHEN bi.business_tax_id_enc IS NOT NULL THEN 1 ELSE 0 END) = 1 AND m.loan_listed = 0 THEN 'checked out' WHEN m.loan_listed = 1 AND (m.all_core_docs_received = 0 AND m.loan_issued = 0) THEN 'listed' WHEN m.all_core_docs_received = 1 AND m.loan_issued = 0 THEN 'docs uploaded' WHEN m.loan_issued = 1 THEN 'issued or approved' ELSE NULL END AS funnel_action,
m.ver_b_revenue AS revenue_last_yr,
m.u_inpt_net_profit AS profit_last_yr,
m.num_of_employees_group_1 AS head_count,
m.m_legal_structure AS legal_structure,
m.years_in_business AS years_in_business,
MKTC.name AS campaign_name,
MKTC.id AS campaign_id
FROM MARKETING.SB_LOANS_DATA_W_PERF_GRP m
LEFT JOIN TLC.LC_LOAN_APP LA ON M.la_loan_id = la.loan_id
LEFT JOIN TLC.LC_actor a ON a.id = la.aid
LEFT JOIN marketing_temp.tv_ad_control_zipcode b ON m.bus_zip = b.zip_code
LEFT JOIN TLC.LC_business_info bi ON bi.id = la.business_info_id
LEFT JOIN TLC.LC_LOAN L ON l.id = la.LOAN_ID
LEFT JOIN TLC.LC_CREDIT_CLASS CC ON cc.id = la.CREDIT_CLASS_ID
LEFT JOIN TLC.LC_CREDIT CR ON cr.id = la.CR_ID
LEFT JOIN TLC.LC_MKT_CAMPAIGN MKTC ON la.REFERER_ID = MKTC.OLD_CAMPAIGN_ID;
Drop view marketing_temp.apps_v2
create view marketing_temp.apps_v2
AS
SELECT ID,
BORROWER_ACTOR_ID,
purpose,
'REGISTERED' AS action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
NULL AS LISTING_DATE,
NULL AS LISTING_AMOUNT,
NULL AS ISSUED_DATE,
NULL AS ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
FROM marketing_temp.apps
WHERE REGISTERED = 1
UNION ALL
SELECT ID,
BORROWER_ACTOR_ID,
purpose,
'COMPLETED_APPS' AS action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
NULL AS LISTING_DATE,
NULL AS LISTING_AMOUNT,
NULL AS ISSUED_DATE,
NULL AS ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
FROM marketing_temp.apps
WHERE COMPLETED_APPS = 1
UNION ALL
SELECT ID,
BORROWER_ACTOR_ID,
purpose,
'LOANS_OFFERED' AS action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
NULL AS LISTING_DATE,
NULL AS LISTING_AMOUNT,
NULL AS ISSUED_DATE,
NULL AS ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
FROM marketing_temp.apps
WHERE LOANS_OFFERED = 1
UNION ALL
SELECT ID,
BORROWER_ACTOR_ID,
purpose,
'LOANS_LISTED' AS action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
LISTING_DATE,
LISTING_AMOUNT,
NULL AS ISSUED_DATE,
NULL AS ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
FROM marketing_temp.apps
WHERE LOANS_LISTED = 1
UNION ALL
SELECT ID,
BORROWER_ACTOR_ID,
purpose,
'all_core_docs_received' AS action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
NULL AS LISTING_DATE,
NULL AS LISTING_AMOUNT,
NULL AS ISSUED_DATE,
NULL AS ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
FROM marketing_temp.apps
WHERE all_core_docs_received_flag = 1
UNION ALL
SELECT ID,
BORROWER_ACTOR_ID,
purpose,
'frontend_decline' AS action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
NULL AS LISTING_DATE,
NULL AS LISTING_AMOUNT,
NULL AS ISSUED_DATE,
NULL AS ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
FROM marketing_temp.apps
WHERE frontend_decline_flag = 1
UNION ALL
SELECT ID,
BORROWER_ACTOR_ID,
purpose,
'backend_decline_flag' AS action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
NULL AS LISTING_DATE,
NULL AS LISTING_AMOUNT,
NULL AS ISSUED_DATE,
NULL AS ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
FROM marketing_temp.apps
WHERE backend_decline_flag = 1
UNION ALL
SELECT ID,
BORROWER_ACTOR_ID,
purpose,
'LOANS_ISSUED' AS action,
APPLICATION_DATE,
PRODUCT,
INTEREST_RATE,
APR,
risk_grade,
LOAN_TERM,
APPLICATION_AMOUNT,
partner_name,
channel_group,
STATE,
ZIP,
ORIG_FEE_FRACTION,
NULL AS LISTING_DATE,
NULL AS LISTING_AMOUNT,
ISSUED_DATE,
ISSUED_AMOUNT,
APP_STATUS,
dma,
City_Area,
TEST_OR_CONTROL,
revenue_last_yr,
profit_last_yr,
head_count,
legal_structure,
years_in_business,
campaign_name,
campaign_id
FROM marketing_temp.apps
WHERE LOANS_ISSUED = 1;
Tableau的“自定义SQL”部分中的代码将为:
从marketing_temp.apps_v2选择*
这解决了Tableau中的手头问题。
关于hadoop - Hive QL在SQL Workbench中运行正常，但在Tableau上失败，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35495491/