CREATE TABLE ABC.SUMMARY AS
(
SELECT
A.NR,
A.DATE,
COUNT(DISTINCT B.ID) TRX_CNT,
MAX(CASE WHEN B.ID IS NOT NULL THEN 1 ELSE 0 END) TRX_AB
FROM BASE A
INNER JOIN TRX_BASE B
ON A.NR = B.NR
WHERE B.TRX_DATE BETWEEN ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), -6) AND TRUNC(CURRENT_DATE,
'MM')-1
GROUP BY A.NR, A.Date
)
WITH DATA PRIMARY INDEX(NR);
CREATE TABLE ABC.SUMMARY AS
(
SELECT
A.NR,
A.DATE,
COUNT(DISTINCT B.ID) TRX_CNT,
MAX(CASE WHEN B.ID IS NOT NULL THEN 1 ELSE 0 END) TRX_AB
FROM BASE A
INNER JOIN TRX_BASE B
ON A.NR = B.NR
WHERE B.TRX_DATE BETWEEN ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), -6) AND TRUNC(CURRENT_DATE,
'MM')-1
GROUP BY A.NR, A.Date
)
WITH DATA PRIMARY INDEX(NR);
Remaining Concerns:
date may/may not be a reserved word. as such I try and avoid columns like that and alias them out to define what the date is for like Base_Date or something.
still unsure if it should be an inner join or left. but as you don't want the nature of the query to change, Inner makes sense as the current query behaves like an inner join. that is becuase if a record existed in base but not in trx_base, it would be eliminated by the B.TRX_Date evaluation as no nulls would be included.
What we removed and why:
Distinct on NR... the group by handles it
1=1 this adds no value and makes no changes to data. it's always true..
Group by spelled out. if someone makes a change to code adding a column in the 1st two columns, this will break.
Removed AS.. it's optional in SQL Server...
CHanged left join to inner as your limit on B.TRX_Date will eliminate all the null values the left join generates: so this is an inner join and changing it doesn't impact results.
发布评论
评论（1）
剩下的担忧：
我们删除的内容以及为什么：
Remaining Concerns:
What we removed and why: