我有下面的查询，如下所示，用雪花编写。现在我发现Pivot功能存在局限性。这是我写的下面的查询。查询的功能看起来不错。
with assettbl as (
select * from asset
where assettype='HAZARD'
and groupname='UNDC'
and status='COMMISSIONED'),
assetspectbl as(
select assetnum,
distance_back,
req_clearance,
pls_cadd_model,
als_survey_date,
weather_case,
last_work_date,
mad,
distrib_company,
risk_ass_date,
temp_mit_in_place
from (
select assetnum,
alnvalue,
assetattrid
from assetspec
)
pivot
(
min(alnvalue)
for assetattrid in ('DISTANCE-BACK' ,
'REQ-C' ,
'PLS-CADD' ,
'ALS-SURVEY-DATE' ,
'WEATHER' ,
'LAST-WORK-DT' ,
'MADDERY' ,
'DISTRIB-COMP' ,
'RISK-ASS' ,
'TEMP-MIT'
)
) AS p(assetnum,
distance_back,
req_clearance,
pls_cadd_model,
als_survey_date,
weather_case,
last_work_date,
mad,
distrib_company,
risk_ass_date,
temp_mit_in_place)
order by assetnum),
locationspectbl as (select classstructureid,
location,
structure_from,
structure_to
from (
select classstructureid,
location,
alnvalue,
assetattrid
from locationspec
where assetattrid in ('ST_FROM' ,'ST_TO'
)
)
pivot
(
min(alnvalue)
for assetattrid in ('ST_FROM',
'ST_TO'
)
) AS p(classstructureid,
location,
structure_from,
structure_to)
order by location),
locationtbl as(
select * from location
),
categorytbl as (
select classstructureid,
location,
structure_category_from
from
(
select classstructureid,
location,
alnvalue,
assetattrid as assetattrid
from locationspec
where assetattrid='LINE-STR'
)
pivot
(
min(alnvalue)
for assetattrid in('LINE-STR')
)
AS p(classstructureid,
location,
structure_category_from
)
order by location),
categorytbl1 as(select classstructureid,
location,
structure_category_to
from (
select classstructureid,location,
alnvalue,
assetattrid as assetattrid
from locationspec
where assetattrid='LINE-STR'
)
pivot
(
min(alnvalue)
for assetattrid in('LINE-STR')
)
AS p(classstructureid,
location,
structure_category_to)
order by location),
main_sql as (select assettbl.assetnum as _key_asset_id
,assettbl.assetnum as "Asset Number"
,assettbl.status as "Asset Status"
,assettbl.tphazstatus as "Hazard Status"
,locationtbl.location as "Location"
,locationtbl.service_area as "Service Area"
,locationtbl.site as "Site"
,locationtbl.region as "Region"
,assetspectbl.distance_back as "Distance (m) in from back structure"
,assetspectbl.req_clearance as "Required clearance"
,assetspectbl.pls_cadd_model as "PLS CADD model"
,assetspectbl.als_survey_date as "ALS Survey Date"
,assetspectbl.weather_case as "Weather Case"
,assetspectbl.last_work_date as "Last Work Date"
,assetspectbl.mad as "MAD"
,assetspectbl.distrib_company as "Distribution Company"
,assetspectbl.risk_ass_date as "Risk Assessment Date"
,assetspectbl.temp_mit_in_place as "Temporary Mitigation in place"
,locationspectbl.structure_from as "Structure From"
,categorytbl.structure_category_from as "Structure Category From"
,locationspectbl.structure_to as "Structure To"
,categorytbl1.structure_category_to as "Structure Category To"
from assettbl
left join assetspectbl on assettbl.assetnum=assetspectbl.assetnum
left join locationtbl on assettbl.location=locationtbl .location
left join locationspectbl on locationspectbl.location=assettbl.location
left join categorytbl on category.location=locationspectbl.structure_from
left join categorytbl1 on categorytbl1 .location=locationspectbl.structure_to
)
select * from main_sql;
上述查询中的问题是我必须扫描和解析类别表2次。有没有最好的方法只扫描一次类别表。
如有任何建议，我们将不胜感激。
问候
好的，所以所有这些时间之后我都重写了您的SQL:
with assettbl as (
select *
from asset
where assettype='HAZARD'
and groupname='UNDC'
and status='COMMISSIONED'
), assetspectbl as(
select
assetnum,
distance_back,
req_clearance,
pls_cadd_model,
als_survey_date,
weather_case,
last_work_date,
mad,
distrib_company,
risk_ass_date,
temp_mit_in_place
from (
select
assetnum,
alnvalue,
assetattrid
from assetspec
)
pivot ( min(alnvalue) for assetattrid in ('DISTANCE-BACK' ,
'REQ-C' ,
'PLS-CADD' ,
'ALS-SURVEY-DATE' ,
'WEATHER' ,
'LAST-WORK-DT' ,
'MADDERY' ,
'DISTRIB-COMP' ,
'RISK-ASS' ,
'TEMP-MIT'
)
) AS p(assetnum, distance_back, req_clearance, pls_cadd_model, als_survey_date,
weather_case, last_work_date, mad, distrib_company, risk_ass_date, temp_mit_in_place)
--order by assetnum
), locationspectbl as (
select
classstructureid,
location,
structure_from,
structure_to
from (
select
classstructureid,
location,
alnvalue,
assetattrid
from locationspec
where assetattrid in ('ST_FROM' ,'ST_TO' )
)
pivot ( min(alnvalue) for assetattrid in ('ST_FROM', 'ST_TO' ) ) AS p(classstructureid, location, structure_from, structure_to)
--order by location
), locationtbl as (
select *
from location
), categorytbl as (
select
classstructureid,
location,
structure_category_from
from (
select
classstructureid,
location,
alnvalue,
assetattrid as assetattrid
from locationspec
where assetattrid = 'LINE-STR'
)
pivot ( min(alnvalue) for assetattrid in('LINE-STR') ) AS p(classstructureid, location, structure_category_from )
--order by location
), categorytbl1 as(
select
classstructureid,
location,
structure_category_to
from (
select
classstructureid,
location,
alnvalue,
assetattrid as assetattrid
from locationspec
where assetattrid = 'LINE-STR'
)
pivot ( min(alnvalue) for assetattrid in('LINE-STR') ) AS p(classstructureid, location, structure_category_to)
--order by location
), main_sql as (
select
assettbl.assetnum as _key_asset_id
,assettbl.assetnum as "Asset Number"
,assettbl.status as "Asset Status"
,assettbl.tphazstatus as "Hazard Status"
,locationtbl.location as "Location"
,locationtbl.service_area as "Service Area"
,locationtbl.site as "Site"
,locationtbl.region as "Region"
,assetspectbl.distance_back as "Distance (m) in from back structure"
,assetspectbl.req_clearance as "Required clearance"
,assetspectbl.pls_cadd_model as "PLS CADD model"
,assetspectbl.als_survey_date as "ALS Survey Date"
,assetspectbl.weather_case as "Weather Case"
,assetspectbl.last_work_date as "Last Work Date"
,assetspectbl.mad as "MAD"
,assetspectbl.distrib_company as "Distribution Company"
,assetspectbl.risk_ass_date as "Risk Assessment Date"
,assetspectbl.temp_mit_in_place as "Temporary Mitigation in place"
,locationspectbl.structure_from as "Structure From"
,categorytbl.structure_category_from as "Structure Category From"
,locationspectbl.structure_to as "Structure To"
,categorytbl1.structure_category_to as "Structure Category To"
from assettbl
left join assetspectbl
on assettbl.assetnum = assetspectbl.assetnum
left join locationtbl
on assettbl.location = locationtbl.location
left join locationspectbl
on locationspectbl.location = assettbl.location
left join categorytbl
on category.location = locationspectbl.structure_from
left join categorytbl1
on categorytbl1.location = locationspectbl.structure_to
)
select *
from main_sql;
我终于看到你的问题了：
您已经注意到CTEcategorytbl
categorytbl as (
select
classstructureid,
location,
structure_category_from
from (
select
classstructureid,
location,
alnvalue,
assetattrid as assetattrid
from locationspec
where assetattrid = 'LINE-STR'
)
pivot ( min(alnvalue) for assetattrid in('LINE-STR') ) AS p(classstructureid, location, structure_category_from )
--order by location
)
与categorytbl1100%相同
只是你有一个不同的参数。ether只使用一个CTE并处理名称差异，或者使第二个只引用第一个，如：
categorytbl as (
select
classstructureid,
location,
structure_category_from
from (
select
classstructureid,
location,
alnvalue,
assetattrid as assetattrid
from locationspec
where assetattrid = 'LINE-STR'
)
pivot ( min(alnvalue) for assetattrid in('LINE-STR') ) AS p(classstructureid, location, structure_category_from )
--order by location
), categorytbl1 as(
select
classstructureid,
location,
structure_category_from as structure_category_to
from categorytbl
)