문제 설명
Where 절을 무시하는 Redshift 교차 조인 (Redshift Cross join ignoring where clause)
다음 쿼리가 있습니다.
WITH MY_CTE as
(
select
....
.....
)
SELECT
MY_CTE.*
,tt.currency as most_used_currency
from MY_CTE
cross join
(select t.currency
from My_CTE t
group by t.currency
order by count(*) desc
limit 1
) tt
where MY_CTE.currency = 'EUR'
하지만 교차 조인이 내 where 절을 무시합니다. 교차 조인 작업을 수행하기 전에 where 절을 처리하도록 하려면 어떻게 해야 하나요?
반환된 샘플 데이터:
SEK 통화를 포함하지 않는다고 말했지만 가장 인기 있는 통화라고 하기 때문에 이것은 분명히 잘못된 것입니다. 저는 이것을 tableau에서 사용할 것이고 사용자가 특정 기준(예: 통화)을 필터링할 수 있어야 하기 때문에 교차 조인 내부에 where 절을 넣을 수 없습니다.
참조 솔루션
방법 1:
WHERE condition in this case has nothing to do with cross join, it just filters rows after join is already performed. If you need to report only single currency there are simplest two options where to add currency filter (added as comments in SQL):
1) Option 1 ‑ add filter already in CTE statement
2) Option 2 ‑ add filter at the end (as already done) and within tt part.
WITH MY_CTE as
(
select
....
.....
/* OPTION 1*/
)
SELECT
MY_CTE.*
,tt.currency as most_used_currency
from MY_CTE
cross join
(select t.currency
from My_CTE t
/* OPTION 2 first place*/
group by t.currency
order by count(*) desc
limit 1
) tt
where MY_CTE.currency = 'EUR' /* OPTION 2a second place*/
방법 2:
The alias tt will return the most popular currency overall, which is SEK. If you want to filter for separate currencies, you'll need to put them in the inner query as well as the outer one. However, if that isn't an option, you'll want to return all currencies with their popularity, and filter on the most popular one you allow.
....
....
SELECT
LAST_VALUE(MY_CTE.customer_id)
OVER (partition by customer_id
ORDER BY tt.popularity
rows between unbounded preceding and unbounded following)
.... /* rest of your columns */
, LAST_VALUE(tt.currency)
OVER (partition by customer_id
ORDER BY tt.popularity
rows between unbounded preceding and unbounded following)
from MY_CTE
cross join
(select t.currency,
count(*) popularity
from My_CTE t
group by t.currency
order by count(*) desc
/* removed limit 1 */
) tt
where MY_CTE.currency = 'EUR'
AND tt.currency IN ('EUR') /* Added tt.currency filter */
(by Lilz、Edgars T.、trafficone)