sql-server - 如何在 2 个带有 GROUP 的 SELECT 语句之间创建 JOIN?
问题描述
我有一张包含每日库存数据的表格,我想按周对其进行总结。每日餐桌如下:
CREATE TABLE [dbo].[TempDaily] (
[Symbol] CHAR (10) NOT NULL,
[CloseDate] DATE NOT NULL,
[DailyHi] FLOAT (53) NULL,
[DailyLow] FLOAT (53) NULL,
[AdjClose] FLOAT (53) NOT NULL,
[WeekEnd] DATE NULL
);
我想在周表中插入一周的摘要:
CREATE TABLE [dbo].[Weekly] (
[Symbol] CHAR (10) NOT NULL,
[WeekEnd] DATE NOT NULL,
[WeeklyHi] FLOAT (53) NOT NULL,
[WeeklyLow] FLOAT (53) NOT NULL,
[AdjClose] FLOAT (53) NULL
);
[AdjClose] 列设置为允许空值,因为我当前的解决方法首先插入其他 4 列,然后使用第三个表中的每周 AdjClose 值更新每周表,这真的很慢。
将前 4 列的每周数据放入表中很简单:
strSQL = "INSERT INTO Weekly (Symbol, WeekEnd, WeeklyHi, WeeklyLow) " &
"SELECT Symbol, WeekEnd, MAX(DailyHi), MIN(DailyLow) " &
"FROM TempDaily " &
"GROUP BY Symbol, WeekEnd "
获取每周 AdjClose 并不那么简单,但我可以将数据插入到临时表中,然后从中更新 Weekly 表:
strSQL = "INSERT INTO Test (WeekEnd, AdjClose) " &
"Select wdata.WeekEnd, MAX(wdata.AdjClose) " &
"FROM " &
"(Select CloseDate, WeekEnd, " &
"FIRST_VALUE(AdjClose) OVER (PARTITION BY WeekEnd ORDER BY CloseDate
DESC ROWS UNBOUNDED PRECEDING) As AdjClose " &
"FROM TempDaily) wdata " &
"GROUP BY wdata.WeekEnd "
我真的更喜欢用一条语句将所有 5 列的数据插入到 Weekly 表中,而不是求助于我笨拙的解决方法,但我无法弄清楚如何加入这 2 条语句。
Daily 表格中的数据采用以下格式(为清楚起见添加了空格):
Symbol, CloseDate, DailyHi,DailyLow,AdjClose, WeekEnd
AAPL ,5/31/2019, 177.99, 175.00, 175.07, 6/1/2019
AAPL ,5/30/2019, 179.23, 176.67, 178.30, 6/1/2019
AAPL ,5/29/2019, 179.35, 176.00, 177.38, 6/1/2019
AAPL ,5/28/2019, 180.59, 177.91, 178.23, 6/1/2019
AAPL ,5/24/2019, 182.14, 178.62, 178.97, 5/25/2019
AAPL ,5/23/2019, 180.54, 177.81, 179.66, 5/25/2019
AAPL ,5/22/2019, 185.71, 182.55, 182.78, 5/25/2019
AAPL ,5/21/2019, 188.00, 184.70, 186.60, 5/25/2019
AAPL ,5/20/2019, 184.35, 180.28, 183.09, 5/25/2019
每周表应以:
Symbol, WeekEnd, WeeklyHi, WeeklyLo, AdjClose
AAPL , 6/1/2019, 180.59, 175.00, 175.07
AAPL , 5/25/2019, 188.00, 177.81, 178.97
我正在将 MS Access 数据库转换为 VB.NET,并认为这将是一个相当简单的端口。差异比我预期的要多得多。任何帮助表示赞赏。
下面介绍的解决方案是我尝试过的解决方案,但它会生成 SQLException:''(' 附近的语法不正确。'wdata' 附近的语法不正确。所以我仍在尝试解决这个问题。
strSQL = "INSERT INTO Weekly (Symbol, WeekEnd, WeeklyHi, WeeklyLow, AdjClose) " &
"Select A.Symbol, A.WeekEnd, A.WeeklyHi, A.WeeklyLow, ISNULL(B.AdjClose, 0) as AdjClose " &
"FROM " &
"(SELECT Symbol, WeekEnd, MAX(DailyHi) as WeeklyHi, MIN(DailyLow) as WeeklyLow " &
"FROM TempDaily " &
"GROUP BY Symbol, WeekEnd ) A " &
"LEFT JOIN " &
"(Select wdata.WeekEnd, MAX(wdata.AdjClose) as AdjClose" &
"FROM " &
"(Select CloseDate, WeekEnd, " &
"FIRST_VALUE(AdjClose) OVER (PARTITION BY WeekEnd ORDER BY CloseDate
DESC ROWS UNBOUNDED PRECEDING) As AdjClose " &
"FROM TempDaily) wdata " &
"GROUP BY wdata.WeekEnd) B ON A.WeekEnd = B.WeekEnd "
解决方案
我认为你可以做你想做的事,像这样加入他们(更新):
strSQL = "INSERT INTO Weekly (Symbol, WeekEnd, WeeklyHi, WeeklyLow, AdjClose) " &
"Select A.Symbol, A.WeekEnd, A.WeeklyHi, A.WeeklyLow, ISNULL(B.AdjClose, 0) as AdjClose " &
"FROM " &
"(SELECT Symbol, WeekEnd, MAX(DailyHi) as WeeklyHi, MIN(DailyLow) as WeeklyLow " &
"FROM TempDaily " &
"GROUP BY Symbol, WeekEnd ) A " &
"LEFT JOIN " &
"(Select wdata.WeekEnd, MAX(wdata.AdjClose) as AdjClose " &
"FROM " &
"(Select CloseDate, WeekEnd, " &
"FIRST_VALUE(AdjClose) OVER (PARTITION BY WeekEnd ORDER BY CloseDate " &
"DESC ROWS UNBOUNDED PRECEDING) As AdjClose " &
"FROM TempDaily) wdata " &
"GROUP BY wdata.WeekEnd) B ON A.WeekEnd = B.WeekEnd "
推荐阅读
- python - 使用 word2vec 和 Kmeans 进行聚类
- http - Angular 5+多个具有相同名称的http参数
- node.js - Angular 2 *ngFor 不打印到表格,我从 GET HTTP 调用中获取我的信息并且它有效
- jquery - 光滑轮播上的视频js使控制栏宽度不完整
- directive - 如何在应用程序组件中调用指令方法
- list - 共享点列表 - 仅允许两个具有相同计划日期或抛出验证消息
- javascript - Django:找到用户时突出显示表格行
- arrays - 将点数组转换为关联数组
- .net-core - 在 Azure 认知服务 api 上获取 404 响应代码
- ios - pangestures 像 iOS 中的邮件应用程序一样交互