数据库表是日K线的数据,KTime是股票上市的那天一直到现在
select KTime,Lowest from DayKLine where datename(yy,KTime)=datename(yy,getdate())and datename(qq,KTime)=datename(qq,getdate())-1 and StockCode='600168'
and Lowest=(select min(cast(Lowest as numeric(5,2))) from DayKLine where datename(yy,KTime)=datename(yy,getdate()) and datename(qq,KTime)=datename(qq,getdate())-1 and StockCode='600168');这句话能取出,当前时间上一季度的最低价和最低价出现的时间!
我的问题是:股票2015-09-30停牌,2016-04-07复牌,要求上一季度的最低价格应该是取2015年4季度的最低价怎么取!
谢谢大家!
select KTime,Lowest from DayKLine where datename(yy,KTime)=datename(yy,getdate())and datename(qq,KTime)=datename(qq,getdate())-1 and StockCode='600168'
and Lowest=(select min(cast(Lowest as numeric(5,2))) from DayKLine where datename(yy,KTime)=datename(yy,getdate()) and datename(qq,KTime)=datename(qq,getdate())-1 and StockCode='600168');这句话能取出,当前时间上一季度的最低价和最低价出现的时间!
我的问题是:股票2015-09-30停牌,2016-04-07复牌,要求上一季度的最低价格应该是取2015年4季度的最低价怎么取!
谢谢大家!
DID int not null,
KTime datetime null,
OpenQuotation varchar(5) null, /*开盘价*/
Highest varchar(5) null, /*最高价*/
Lowest varchar(5) null, /*最低价*/
CloseQuotation varchar(5) null, /*收盘价*/
constraint PK_DAYKLINE primary key (DID)
)
go
insert into DayKLine values(1,'2015-09-30','9.58','9.63','9.46','9.54');
insert into DayKLine values(2,'2016-04-07','11.36','11.58','10.91','11.58');求本季度最低价与上一季度最低价的比值,本季度是2016年第二季度,上季度是2015年第四季度(因为中间有停牌,没有停牌就正常计算!)不知道说清楚了没有,谢谢版主!
DECLARE @DayKLine TABLE(
DID int not null,
KTime datetime null,
OpenQuotation varchar(5) null, /*开盘价*/
Highest varchar(5) null, /*最高价*/
Lowest varchar(5) null, /*最低价*/
CloseQuotation varchar(5) null, /*收盘价*/
StockCode varchar(6))
insert into @DayKLine values(1,'2015-09-29','9.58','9.63','9.56','9.54','600168');
insert into @DayKLine values(2,'2015-09-30','9.58','9.63','9.46','9.54','600168');
insert into @DayKLine values(3,'2016-04-07','11.36','11.58','10.91','11.58','600168');
insert into @DayKLine values(4,'2016-04-08','11.36','11.58','11.91','11.58','600168');-- 季度统计 --
DECLARE @QuarterLine TABLE(
StockCode varchar(6) not null,
[Year] int not null,
[Quarter] int not null,
Lowest numeric(5,2) null,
LowestDay datetime null
);WITH A AS (
SELECT StockCode,
KTime,
Year(KTime) [Year],
DatePart(q,KTime) [Quarter],
CAST(lowest AS numeric(5,2)) Lowest
FROM @DayKLine
),
B AS (
SELECT StockCode,
[Year],
[Quarter]
FROM A
GROUP BY StockCode, [Year], [Quarter]
)
INSERT INTO @QuarterLine
SELECT *
FROM B
CROSS APPLY (
SELECT TOP 1 Lowest, KTime
FROM A
WHERE B.StockCode = A.StockCode
AND B.[Year] = A.[Year]
AND B.[Quarter] = A.[Quarter]
ORDER BY Lowest, KTime
) C-- 比较 --
;WITH E AS (
SELECT ROW_NUMBER() OVER(PARTITION BY StockCode
ORDER BY [Year], [Quarter]
) rn,
*
FROM @QuarterLine
)
SELECT E1.StockCode,
E1.[Year], E1.[Quarter], E1.Lowest, E1.LowestDay, -- 本季度
E2.[Year], E2.[Quarter], E2.Lowest, E2.LowestDay -- “上”季度
FROM E E1
LEFT JOIN E E2
ON E1.StockCode = E2.StockCode
AND E1.rn = E2.rn + 1
WHERE E1.[Year] = Year(GetDate())
AND E1.[Quarter] = DatePart(q,GetDate())
AND E1.StockCode = '600168'
StockCode Year Quarter Lowest LowestDay Year Quarter Lowest LowestDay
--------- ----------- ----------- ---------- ---------- ----------- ----------- ---------- ----------
600168 2016 2 10.91 2016-04-07 2015 3 9.46 2015-09-30