# پایگاه‌های داده > SQL Server > T-SQL >  جمع اعداد منفی و مثبت به صورت سری و جداگانه

## mehrjuisaac

یک جدول دارم که اطلاعات به صورت روزانه در آن ذخیره می شود. اطلاعات مالی می باشد که قیمت امروز نسبت به دیروز میتونه بیشتر یا کمتر باشه یعنی نسبت رشد هم می تواند مثبت باشد و هم منفی باشد. حال می خواهم یک query که  یه جدول خلاصه داشته باشم که تعداد روزهای مثبت پشت سرهم را بشمارد و جمع آن را محاسبه کند.سپس منفی به همین ترتیب.
مانند تصویر نمونه
جدول اصلی: 

جدول خلاصه شده:

----------


## hamid_hr

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


اینو با کرسر درست کردم
همینقدر به ذهنم رسید
شاید اساتید راه بهتری بلد باشن

----------


## N_D

به دو روش 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

----------


## tooraj_azizi_1035

سلام

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

----------


## starting

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);

----------


## mehrjuisaac

با سلام متشکر از جواب همه دوستان که زحمت کشیدنیه سوال داشتم از جناب *starting*  می خواستم ببینم منطق این قسمت از برنامه چه می باشد چرا از هم تفریق شده اند

ROW_NUMBER() OVER(PARTITION BY name ORDER BY ActionDate)        - ROW_NUMBER() OVER(PARTITION BY name ORDER BY SIGN(percentage)) AS grp

----------


## starting

سلام،
شما قصد دارید سطرهای مرتبط به هم را گروه بندی کنید دیگه. سطرهایی که بطور متوالی منفی یا مثبت هستند در یکر گروه قرار می گیرند. این فرمولی که مشاهده می کنید کارش تشخیص و تفکیک کردن گروه ها از همه. گروه هایی که دارای ویژگی و خصیصه های یکسانی هستند یعنی همه منفی یا مثبت بوده و همگی پشت سرهم و بصورت متوالی قرار گرفتن.

خیلی قشنگ مساله حل شده درسته؟ باید از توابع ویندو ممنون باشیم که مساله های پیچیده را توسط اونها بسادگی می تونیم حل کنیم.

----------


## fakhravari

اگه فقط عدد داشتیم ؟
یعنی جدول فقط یه فیلد عدد داشته باشه!

----------


## starting

> اگه فقط عدد داشتیم ؟
> یعنی جدول فقط یه فیلد عدد داشته باشه!


نمیشه که. حتما باید یک فیلد باشه که ترتیب و sequence داده ها را تعیین کنه. پشت سرهم بودن در اینجا توسط داده های یک فیلد تعیین میشه. همینطوری که نیست.

----------


## fakhravari

نشدن که نباید تو کارش باشه!!!

----------


## N_D

دوستان عزیز متاسفانه همه جوابهای بالا خروجی اشتباه دارند از جمله جواب خودم .. کافیه که مقادیر رو عوض کنیم 
 مهمترین خواسته این سوال اینه که توالی علامت ها دقیقا رعایت بشه چون این دوستمون می خواد نمودار بازدهی بورس رو بکشه 
برای نمونه من داده های جدید رو قرار میدم. باز هم تاکید میکنم توالی تعداد مثبت ها و منفی ها باید رعایت بشه در غیر اینصورت نمودار اشتباه نمایش داده میشه..

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

----------


## starting

جناب 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);

----------


## N_D

از 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

 برای سرمایه گذاری روی شرکت هایی که در بورس هستند باید حواستون باشه که اخیرا چقدر از سهامشون فوش رفته یا متقاضی خرید یا فروش داشته از این روش میشه تصمیم گرفت که فلان سهک رو بخریم یا نه؟

----------


## starting

فرض بگیرید بنده میخوام سهام بورس خریداری کنم. آیا این اطلاعات در دسترس عموم قرار میگیره؟ که من بخوام این داده ها را آنالیز کنم؟

----------


## fakhravari

بعدش دوستان به سوال منم جواب بدن

----------


## starting

> بعدش دوستان به سوال منم جواب بدن


 سوالتون رو دقیق و با جزئیات مطرح کنید تا بشه جواب بهش داد.
الان دقیقه با چه مساله ای روبرو هستید؟ می تونید یک نمونه داده و نتیجه مطلوب خود را برایمان پست کنید؟

----------


## N_D

بورس در روزهای غیر تعطیل از ساعت 9 تا 12:30 فعالیت داره که اطلاعات لحظه ای اونو تو سایت TSETMC.COM پیدا کنی

----------


## starting

> بورس در روزهای غیر تعطیل از ساعت 9 تا 12:30 فعالیت داره که اطلاعات لحظه ای اونو تو سایت TSETMC.COM پیدا کنی


واقعا ازتون تشکر می کنم. در یکی از بخش های سایت مذکور خروجی داده ها در قالب فایلهای سازگار با Excel برای دسترس عموم قرار داده شده.

----------


## fakhravari

یه فیلد عدد که + و -  داریم.
جمع منفی / جمع مثبت میخواهم

----------


## N_D

> یه فیلد عدد که + و - داریم.
> جمع منفی / جمع مثبت میخواهم


دوست عزیز اگه با اسکریپت دیتای مورد نظر رو وارد کنی بهتر میتونیم کمک کنیم

----------


## starting

شاید این کمکتون کنه:
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

----------


## mehrjuisaac

با سلام به تمامی کارشناسان خبره 
من مدتی قبل تو بورس وارد شدم با توجه  به خرید هایی که کردم اول سود داشتم ولی با توجه به عدم استفاده از داده  های موجدی  بعدش کلی ضرر دادم به این نتیجه رسیدم از این به بعد کل خریدها  را با تجزیه و تحلیل انجام بدم با توجه به اینکه رشته تحصیلی ام نرم افزار  است تصمبم به تهیه نرم افزاری دارم که بهترین سهم هایی که تو بازار هست  بخرم خوب حالا اگه از دوستان کسی اکه هست که بخواد با هم همکاری کنیم من در  خدمت دوستان هستم.

----------


## starting

هدفتان از ایجاد این نرم افزار دقیقه چیه؟ هدف مالی هم دارید؟
من در تهیه گزارشات آماری و تحلیل آن با 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)

----------


## fahimi

سلام
فکر کنم با Subquery این کار می توانید بکنید.
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

----------


## محمد قانعی

با تشکر از همگی به خصوص آقای فهیمی.
من با Subquery نتونستم کار کنم برای همون اومدم جدولم رو تغییر دادم و دو ستون برای مبالغ بدهکار و بستانکار درست کردم.
حالا میخوام در دستور  select جمع دو ستون رو در یه ستون دیگه بیارم.
بد
بس
جمع

10
0
10

0
11
1-

----------


## N_D

این آخرشو متوجه نشدم -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 ایراد پیدا کرده.
Untitled1.png

باید اولین رکوردش 10 باشه رکورد بعدیش جمعش 18  و رکورد بعدش هم 13 باشه.احتمالاٌ بابت حذف همون order by هستش!
اگه تونستی کدهای خط 8 ( sum )رو یکم بهم توضیح بده.

----------


## N_D

بله دليلش حذف order by  هست. من اين اسكريژت رو توي 2012 نوشتم . ورژن sql  شما چيه؟

----------


## محمد قانعی

من  SQL 2008 R2 نصب دارم.حالا باید چیکار کنم؟ :متفکر:

----------


## N_D

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

----------

