Printable View
یک جدول دارم که اطلاعات به صورت روزانه در آن ذخیره می شود. اطلاعات مالی می باشد که قیمت امروز نسبت به دیروز میتونه بیشتر یا کمتر باشه یعنی نسبت رشد هم می تواند مثبت باشد و هم منفی باشد. حال می خواهم یک query که یه جدول خلاصه داشته باشم که تعداد روزهای مثبت پشت سرهم را بشمارد و جمع آن را محاسبه کند.سپس منفی به همین ترتیب.
مانند تصویر نمونه
جدول اصلی:
http://upcity.ir/images2/74192208367331211896.png
جدول خلاصه شده:
http://upcity.ir/images2/14097114489027264709.png
declare @tbl table(IDPK int identity(1,1),Name varchar(20),Precent float)
declare @tbl1 table(IDPK int Identity(1,1), Name varchar(10),Precent float,Number int)
declare @name varchar(20), @precent float,@IDPK int, @precent2 float, @name1 varchar(20)
insert into @tbl select 'a',-3.5
insert into @tbl select 'a',3.5
insert into @tbl select 'a',2.5
insert into @tbl select 'a',0.5
insert into @tbl select 'a',-1.5
insert into @tbl select 'a',-5.5
insert into @tbl select 'a',-0.05
insert into @tbl select 'a',2.5
insert into @tbl select 'a',0.5
insert into @tbl select 'a',1.5
insert into @tbl select 'a',-3.5
--select * from @tbl
declare MyCursor10 Cursor for
select IDPK from @tbl order by IDPK;
open MyCursor10;
fetch next from MyCursor10 into @idpk;
while @@FETCH_STATUS = 0
begin
set @precent2 = null
select @name = Name,@precent = precent from @tbl where @IDPK = IDPK
select top(1) @precent2 = Precent,@name1 = Name from @tbl1 order by IDPK desc
if(@precent2 is null)
insert into @tbl1 select @name, @precent,1
else if(@name1 != @name)
insert into @tbl1 select @name, @precent,1
else if((@precent > 0 and @precent2 < 0 ) or(@precent < 0 and @precent2 > 0 ))
insert into @tbl1 select @name, @precent,1
else
begin
update @tbl1 set Precent = Precent + @precent, Number = Number + 1
where IDPK = (select MAX(idpk) from @tbl1)
end
fetch next from MyCursor10 into @idpk;
end
CLOSE MyCursor10;
DEALLOCATE MyCursor10;
select * from @tbl
select * from @tbl1
اینو با کرسر درست کردم
همینقدر به ذهنم رسید
شاید اساتید راه بهتری بلد باشن
به دو روش 2012 و 2008 برات می نویسم
دوست عزیز از این به بعد زحمت بکش اگه چنین سوالاتی داشته اسکریپت تولید دیتا رو هم بنویس تا برامون راحت تر باشه..
اول به روش 2012
CREATE TABLE T1(ActionDate Int, Name nvarchar(10), Percentage decimal(18,2) )
INSERT T1 VALUES
(20120403,'a',-3.57),
(20120404,'a',-0.08),
(20120407,'a',-3.63),
(20120408,'a',1.70),
(20120409,'a',3.97),
(20120410,'a',3.97),
(20120411,'a',-2.64),
(20120403,'b',-0.08),
(20120404,'b',-2.27),
(20120407,'b',-3.77),
(20120408,'b',-1.41),
(20120409,'b',3.91),
(20120410,'b',3.53),
(20120411,'b',-2.10);
WITH CTE ( RW, PriorPercentage, Name, Percentage ) AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ActionDate),
ISNULL( LAG(Percentage) OVER (PARTITION BY Name ORDER BY ActionDate),Percentage),
Name, Percentage
FROM T1
),
CTE_REC(RW, Name, Percentage, Grp) AS
(
SELECT RW, Name, Percentage, 0 Grp FROM CTE
WHERE RW =1
UNION ALL
SELECT A.RW , A.Name, A.Percentage, B.Grp + CASE WHEN SIGN(A.Percentage) <> SIGN(A.PriorPercentage) THEN 1 ELSE 0 END FROM CTE A
INNER JOIN CTE_REC B ON A.RW = B.Rw+1 and a.Name = b.Name
)
SELECT DISTINCT
Name,
'SumGroup'=SUM(Percentage) OVER(PARTITION BY Name,GRP),
'CountGroup'=COUNT(Percentage) OVER(PARTITION BY Name,GRP)
FROM CTE_REC
و به روش 2008 چون 2008 از دستور LAG پشتیبانی نمی کند
CREATE TABLE T1(ActionDate Int, Name nvarchar(10), Percentage decimal(18,2) );
INSERT T1 VALUES
(20120403,'a',-3.57),
(20120404,'a',-0.08),
(20120407,'a',-3.63),
(20120408,'a',1.70),
(20120409,'a',3.97),
(20120410,'a',3.97),
(20120411,'a',-2.64),
(20120403,'b',-0.08),
(20120404,'b',-2.27),
(20120407,'b',-3.77),
(20120408,'b',-1.41),
(20120409,'b',3.91),
(20120410,'b',3.53),
(20120411,'b',-2.10);
WITH CTE_T1 AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ActionDate) as RW,
ActionDate,Name, Percentage
FROM T1
),
CTE ( RW, PriorPercentage, Name, Percentage ) AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY A.Name ORDER BY A.ActionDate),
ISNULL(B.Percentage,A.Percentage) as PriorPercentage,
A.Name, A.Percentage
FROM CTE_T1 A
LEFT JOIN CTE_T1 B ON A.Name = B.Name and A.RW = B.RW+1
)
,
CTE_REC(RW, Name, Percentage, Grp) AS
(
SELECT RW, Name, Percentage, 0 Grp FROM CTE
WHERE RW =1
UNION ALL
SELECT A.RW , A.Name, A.Percentage, B.Grp + CASE WHEN SIGN(A.Percentage) <> SIGN(A.PriorPercentage) THEN 1 ELSE 0 END FROM CTE A
INNER JOIN CTE_REC B ON A.RW = B.Rw+1 and a.Name = b.Name
)
SELECT DISTINCT
Name,
'SumGroup'=SUM(Percentage) OVER(PARTITION BY Name,GRP),
'CountGroup'=COUNT(Percentage) OVER(PARTITION BY Name,GRP)
FROM CTE_REC
سلام
select distinct name, sum(percentage) over (partition by name, sgn, dp-rn) sumpercent, COUNT(percentage) over
(partition by name, sgn, dp-rn) countpercent from (
select *, ROW_NUMBER() over (order by (select 0)) rn from (
SELECT TOP 1000 [id]
,[percentage]
,[name]
,ROW_NUMBER() over (order by name)) dp, sign(percentage) sgn
FROM [Test].[dbo].[Table_1]
order by name, sign(percentage)) t1
) t2
order by name
SELECT name,
SUM(percentage) AS SumPerecnt,
COUNT(percentage) AS CountPercent
FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY name ORDER BY ActionDate)
- ROW_NUMBER() OVER(PARTITION BY name ORDER BY SIGN(percentage)) AS grp
FROM T1
)D
GROUP BY name, grp
ORDER BY name, MIN(ActionDate);
با سلام متشکر از جواب همه دوستان که زحمت کشیدنیه سوال داشتم از جناب starting می خواستم ببینم منطق این قسمت از برنامه چه می باشد چرا از هم تفریق شده اند
ROW_NUMBER() OVER(PARTITION BY name ORDER BY ActionDate) - ROW_NUMBER() OVER(PARTITION BY name ORDER BY SIGN(percentage)) AS grp
سلام،
شما قصد دارید سطرهای مرتبط به هم را گروه بندی کنید دیگه. سطرهایی که بطور متوالی منفی یا مثبت هستند در یکر گروه قرار می گیرند. این فرمولی که مشاهده می کنید کارش تشخیص و تفکیک کردن گروه ها از همه. گروه هایی که دارای ویژگی و خصیصه های یکسانی هستند یعنی همه منفی یا مثبت بوده و همگی پشت سرهم و بصورت متوالی قرار گرفتن.
خیلی قشنگ مساله حل شده درسته؟ باید از توابع ویندو ممنون باشیم که مساله های پیچیده را توسط اونها بسادگی می تونیم حل کنیم.
اگه فقط عدد داشتیم ؟
یعنی جدول فقط یه فیلد عدد داشته باشه!
نمیشه که. حتما باید یک فیلد باشه که ترتیب و sequence داده ها را تعیین کنه. پشت سرهم بودن در اینجا توسط داده های یک فیلد تعیین میشه. همینطوری که نیست.نقل قول:
نوشته شده توسط fakhravari
نشدن که نباید تو کارش باشه!!!
دوستان عزیز متاسفانه همه جوابهای بالا خروجی اشتباه دارند از جمله جواب خودم .. کافیه که مقادیر رو عوض کنیم
مهمترین خواسته این سوال اینه که توالی علامت ها دقیقا رعایت بشه چون این دوستمون می خواد نمودار بازدهی بورس رو بکشه
برای نمونه من داده های جدید رو قرار میدم. باز هم تاکید میکنم توالی تعداد مثبت ها و منفی ها باید رعایت بشه در غیر اینصورت نمودار اشتباه نمایش داده میشه..
DROP TABLE T1
GO
CREATE TABLE T1(ActionDate Int, Name nvarchar(10), Percentage decimal(18,2) );
INSERT T1 VALUES
(20120403,'a',-3.57),
(20120404,'a', 0.08),
(20120407,'a',-3.63),
(20120408,'a',1.70),
(20120409,'a',3.97),
(20120410,'a',3.97),
(20120411,'a',-2.64),
(20120403,'b',0.08),
(20120404,'b',-2.27),
(20120407,'b',-3.77),
(20120408,'b',-1.41),
(20120409,'b',3.91),
(20120410,'b',3.53),
(20120411,'b',-2.10);
GO
من هم روش کار کردم و یه جواب براش نوشتم . دوستان تست کنند و نظراتشون رو بدن
WITH CTE ( RW, PriorPercentage, Name, Percentage ) AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ActionDate),
ISNULL( LAG(Percentage) OVER (PARTITION BY Name ORDER BY ActionDate),Percentage),
Name, Percentage
FROM T1
),
CTE_REC(RW, Name, Percentage, Grp) AS
(
SELECT RW, Name, Percentage, 0 Grp FROM CTE
WHERE RW =1
UNION ALL
SELECT A.RW , A.Name, A.Percentage, B.Grp + CASE WHEN SIGN(A.Percentage) <> SIGN(A.PriorPercentage) THEN 1 ELSE 0 END FROM CTE A
INNER JOIN CTE_REC B ON A.RW = B.Rw+1 and a.Name = b.Name
),
CTE_Final AS
(
SELECT
Name,
'SumGroup'=SUM(Percentage) OVER(PARTITION BY Name,GRP),
'CountGroup'=COUNT(Percentage) OVER(PARTITION BY Name,GRP)
,RW
FROM CTE_REC
)
SELECT
Name, SumGroup ,CountGroup
FROM
(
SELECT TOP 10000000000
Name, SumGroup ,CountGroup
,RW2 = ROW_NUMBER() OVER( Partition By NAme,SumGroup,CountGroup Order by Name)
FROM CTE_Final
ORDER BY Name,RW
) T
WHERE RW2=1
جناب N_D حق با شماست.
کوئری شما حداقل در ظاهر که صحیح است.
کوئری خودم را نیز اصلاح کردم. نتیجه اش کاملا مطابق با نتیجه شماست.
راستی این نمودار بازدهی بورس قضیه اش چیه؟ میشه کمی راجع به این موضوع برامون بگی؟نقل قول:
نمودار بازدهی بورس
ضمنا به جای اینکه کلی صفر جلوی TOP بزاری کافیه بنویسی TOP 100 Percent.
SELECT name,
SUM(percentage) AS SumPerecnt,
COUNT(percentage) AS CountPercent
FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY name ORDER BY ActionDate)
- ROW_NUMBER() OVER(PARTITION BY name ORDER BY CASE WHEN percentage < 0 THEN 0 ELSE 1 END, actiondate) AS grp
FROM T1
)D
GROUP BY name, grp
ORDER BY name, MIN(ActionDate);
از SQL 2005 گزینه top 100 percent توی VIEW و SUBQUERY مشکل داره واسه همین هم من این کد رو نوشتم..
برای امتحان یه view بسازین بعدش از روی viewکوئری Order by یگیرین
CREATE TABLE Table1(id int, Name nvarchar(100))
GO
INSERT Table1 VALUES(10,'Sasan'),(14,'Ali'),(3,'Reza')
GO
CREATE VIEW VW_TEST AS
SELECT TOP 100 PERCENT
id, Name FROM Table1
ORDER BY Name
GO
SELECT * FROM VW_TEST
برای سرمایه گذاری روی شرکت هایی که در بورس هستند باید حواستون باشه که اخیرا چقدر از سهامشون فوش رفته یا متقاضی خرید یا فروش داشته از این روش میشه تصمیم گرفت که فلان سهک رو بخریم یا نه؟
فرض بگیرید بنده میخوام سهام بورس خریداری کنم. آیا این اطلاعات در دسترس عموم قرار میگیره؟ که من بخوام این داده ها را آنالیز کنم؟
بعدش دوستان به سوال منم جواب بدن
سوالتون رو دقیق و با جزئیات مطرح کنید تا بشه جواب بهش داد.نقل قول:
نوشته شده توسط fakhravari
الان دقیقه با چه مساله ای روبرو هستید؟ می تونید یک نمونه داده و نتیجه مطلوب خود را برایمان پست کنید؟
بورس در روزهای غیر تعطیل از ساعت 9 تا 12:30 فعالیت داره که اطلاعات لحظه ای اونو تو سایت TSETMC.COM پیدا کنی
واقعا ازتون تشکر می کنم. در یکی از بخش های سایت مذکور خروجی داده ها در قالب فایلهای سازگار با Excel برای دسترس عموم قرار داده شده.نقل قول:
نوشته شده توسط N_D
یه فیلد عدد که + و - داریم.
جمع منفی / جمع مثبت میخواهم
دوست عزیز اگه با اسکریپت دیتای مورد نظر رو وارد کنی بهتر میتونیم کمک کنیمنقل قول:
یه فیلد عدد که + و - داریم.
جمع منفی / جمع مثبت میخواهم
شاید این کمکتون کنه:
USE TempDB
Go
CREATE TABLE test (columnName INT);
INSERT INTO test VALUES (-10), (-20), (5), (15), (0);
SELECT *
FROM (SELECT columnName,
SIGN(columnName) S
FROM test)D
PIVOT (SUM(columnName) FOR S IN ([-1],[0],[1]))S
-1 0 1
----------- ----------- -----------
-30 0 20
با سلام به تمامی کارشناسان خبره
من مدتی قبل تو بورس وارد شدم با توجه به خرید هایی که کردم اول سود داشتم ولی با توجه به عدم استفاده از داده های موجدی بعدش کلی ضرر دادم به این نتیجه رسیدم از این به بعد کل خریدها را با تجزیه و تحلیل انجام بدم با توجه به اینکه رشته تحصیلی ام نرم افزار است تصمبم به تهیه نرم افزاری دارم که بهترین سهم هایی که تو بازار هست بخرم خوب حالا اگه از دوستان کسی اکه هست که بخواد با هم همکاری کنیم من در خدمت دوستان هستم.
هدفتان از ایجاد این نرم افزار دقیقه چیه؟ هدف مالی هم دارید؟
من در تهیه گزارشات آماری و تحلیل آن با Excel و SQL آمادم. خواستید پیام خصوصی بدید.
با سلام.
من توی جدولم برای مبلغ فقط یک فیلد ایجاد کردم که مقدار - و + میگیره.
حالا توی دستور select می خوام منفی توی یک ستون و مثبت توی یک ستون باشه.مثال:توی جدولم (نام - مبلغ ) ذخیره میشه و توی select قراره ( نام - بدهکار -بستانکار) نمایش داده بشه!
چه جوری این کار رو انجام بدم؟
من میخوام این دو تا جدول رو تو یک جدول دو ستونی بیارم.
(select monye from tbl_gardesh_s_h_d where monye>0)
(select monye from tbl_gardesh_s_h_d where monye<0)
سلام
فکر کنم با Subquery این کار می توانید بکنید.
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx
با تشکر از همگی به خصوص آقای فهیمی.
من با Subquery نتونستم کار کنم برای همون اومدم جدولم رو تغییر دادم و دو ستون برای مبالغ بدهکار و بستانکار درست کردم.
حالا میخوام در دستور select جمع دو ستون رو در یه ستون دیگه بیارم.
بد بس جمع 10 0 10 0 11 1-
این آخرشو متوجه نشدم -1 از کجا اومد اگه هم قراره مجموع جبری باشه پش گروه بندیش کجاست؟ باید بر اساس یه چیزی مثل شماره سند گروه بندی بشه اگه همینطوره این کدشه
Drop table #t;
Create table #t ( Id int, DocId int, Price int);
Insert #t values(1,1, 10),(2,1, 8),(3,1, -5),(4,1, -10),(5,2, 12),(6,2, -8),(7,2, 10)
Select Id,DocId,
Case When Price>=0 Then Price Else 0 End as Bes,
Case When Price< 0 Then -Price Else 0 End as Bed,
SUM(Price) OVER( Partition By DocId order by Id ) as A
From #t
خیلی عالی بود . دستت درد نکنه n_d عزیز.
فقط قسمت order by خطا میده! منم حذفش کردم و دیگه پیغام نداد.
فقط خروجیه ستون A ایراد پیدا کرده.
ضمیمه 124300
باید اولین رکوردش 10 باشه رکورد بعدیش جمعش 18 و رکورد بعدش هم 13 باشه.احتمالاٌ بابت حذف همون order by هستش!
اگه تونستی کدهای خط 8 ( sum )رو یکم بهم توضیح بده.
بله دليلش حذف order by هست. من اين اسكريژت رو توي 2012 نوشتم . ورژن sql شما چيه؟
من SQL 2008 R2 نصب دارم.حالا باید چیکار کنم؟:متفکر:
Drop table #t;
GO
Create table #t ( Id int, DocId int, Price int, A int);
Insert #t(id, DocId, Price) values(1,1, 10),(2,1, 8),(3,1, -5),(4,1, -10),(5,2, 12),(6,2, -8),(7,2, 10)
Declare @A int=0 , @DocId int =0;
update #t
SET
@A = A= Case DocId WHEN @DocId THEN @A ELSE 0 END + Price,
@DocId = DocId
Select * From #t