我得到的示例SQL似乎并不能满足我的需求。
大表有400万行,小表有60万行。
/*示例代码:(我是由一位高级分析师提供的) */
SELECT SUM(BigTable.VALUE)
FROM BigTable INNER JOIN SmallTable
WHERE BigTable.ID = SmallTable.ID
AND BigTable.VALUATION_DATE IN
(SELECT MAX(VALUATION_DATE)
FROM BigTable)
GROUP BY BigTable.ID
/*我的代码:(我在内部查询中放置了一个WHERE ) */
SELECT BigTable.ID, SUM(BigTable.VALUE)
FROM BigTable INNER JOIN SmallTable
WHERE BigTable.ID = SmallTable.ID
AND BigTable.VALUATION_DATE IN
(SELECT MAX(VALUATION_DATE)
FROM BigTable INNER JOIN SmallTable
WHERE BigTable.ID = SmallTable.ID)
GROUP BY BigTable.ID
如果ID xyz在最近日期有三个帐户,值分别为$1、$2、$3,我想返回该日期所有帐户的总和: xyz,$6
发布于 2019-06-11 03:19:07
您应该避免使用where子句,而使用ON子句
SELECT SUM(BigTable.VALUE)
FROM BigTable
INNER JOIN SmallTable ON BigTable.ID = SmallTable.ID
AND BigTable.VALUATION_DATE = (
SELECT MAX(VALUATION_DATE)
FROM BigTable)
并且您不应该使用按id分组。
发布于 2019-06-11 03:23:40
使用窗口函数:
SELECT b.ID, b.VALUE
FROM (SELECT b.*,
ROW_NUMBER() OVER (PARTITION BY b.id ORDER BY b.VALUATION_DATE DESC) as seqnum
FROM BigTable b
) b JOIN
SmallTable s
ON b.ID = s.ID
WHERE b.seqnum = 1;
我不认为聚合是必要的。但是,如果同一个id在同一日期有多个值,那么:
SELECT b.ID, SUM(b.VALUE)
FROM (SELECT b.*,
RANK() OVER (PARTITION BY b.id ORDER BY b.VALUATION_DATE DESC) as seqnum
FROM BigTable b
) b JOIN
SmallTable s
ON b.ID = s.ID
WHERE b.seqnum = 1
GROUP BY b.id;
https://stackoverflow.com/questions/56532141
复制相似问题