ایجاد یک ستون برای جمع سطرهای قبل؟

[SYNDROME]

با سلام
من می خواهم بر روی یک جدول مانند جدول زیر یک Query ایجاد کنم که یک ستون داشته باشد که جمع سطرهای قبل را نمایش دهد.


ID Name Price Sum1
1 A 10 0
2 B 20 10
3 C 30 30
4 D 40 60

دقت کنید که ستون Sum1 جمع ستون Price سطرهای قبل را نمایش می دهد.
دستوری که من استفاده می کنم.


Select ID,Name,Price,
( Select Sum(Price)
From Tbl_Test
Where ID < T1.ID
) As Sum1
From Tbl_Test T1

این دستور درست اصلی ولی مشکل آن سرعت که با بالا رفتن تعداد رکوردها شدیداً پایین می آید.
البته کد بالا یک مثال است و دستورات بنده خیلی پیچیده تر است و در 300000 رکورد شدیداً کند می شود.
آیا راه حل دیگری وجود دارد که این کد ساده تر شود و سرعت اجرای آن کاهش یابد.
منتظر راهنمایی دوستان هستم

[AminSobati]

فقط Cursor!

[amin_alexi]

منم این مشکل رو دارم !

فقط Cursor!
شرمنده من خیلی متوجه نشدم ! :گیج:
یعنی به چه صورت !؟ :متفکر:
مثلا به جای استفاده از این

( Select Sum(Price)
From Tbl_Test
Where ID < T1.ID
) As Sum1
یه Function بنویسیم که سطر به سطر تا سطر مورد نظر ما بره و مقادیر رو جمع بزنه ! :متفکر:
.
.
اصلا میشه کاری کرد که هر بار نره از اول جمع بزنه !؟ :اشتباه:
مقدار جمع ستون قبل رو به چه طریق میشه بدست آورد !؟:ناراحت:

[تولائی]

پیشنهاد من :


declare @tbl table(Id int, Name varchar(50), price int, sum1 int)
insert into @tbl select *,0 from tbl_test
declare @id int, @price int, @sum int
set @sum = 0
DECLARE sum_cursor CURSOR
FOR SELECT id, price FROM @tbl
OPEN sum_cursor
FETCH NEXT FROM sum_cursor
INTO @id, @price
WHILE @@FETCH_STATUS = 0
BEGIN
update @tbl set sum1 = @sum where Id = @Id
select @sum = @sum + @price
FETCH NEXT FROM sum_cursor
INTO @id, @price
END
CLOSE sum_cursor
DEALLOCATE sum_cursor
select * from @tbl

[SYNDROME]

فقط Cursor!
با تشکر از آقا امین و سایر دوستان
امین جان اگر امکان دارد یک مثال در این زمینه بزنید تا بنده هم بهتر متوجه بشوم.
با تشکر

[ar.shirazi]

فقط Cursor!
راه بهتری هم هست.

ایشان میتوانند با استفاده از یک متغیر از نوع table که دارای دو ستون به نامهای ID, Sum است، در هر سطر ID و جمع سطرهای قبلی تا آنجا را ذخیره کنند. نه این که برای هر سطر مجبور باشند از اول جدول همه مقادیر را با هم جمع کنند

[amin_alexi]

من روش تولائی (http://barnamenevis.org/forum/member.php?u=86415) رو تست کردم
کار کرد به نظر کار جالبی میاد !
حالا برم رو حجم بالا بررسی کنم و نتیجه رو با روش قبلی مقایسه کنم ! :لبخند:

نقل قول: ایجاد یک ستون برای جمع سطرهای قبل؟
نقل قول:
نوشته شده توسط AminSobati http://barnamenevis.org/forum/images/buttons/viewpost.gif (http://barnamenevis.org/forum/showthread.php?p=654274#post654274)
فقط Cursor!

راه بهتری هم هست.

ایشان میتوانند با استفاده از یک متغیر از نوع table که دارای دو ستون به نامهای ID, Sum است، در هر سطر ID و جمع سطرهای قبلی تا آنجا را ذخیره کنند. نه این که برای هر سطر مجبور باشند از اول جدول همه مقادیر را با هم جمع کنند
فکر کنم منظور شما هم همین روش تولائی (http://barnamenevis.org/forum/member.php?u=86415) باشه ... این هم همین کار رو میکنه
اگه دوستان روشه بهترتری :لبخند: دارد بگین ...
:تشویق:

[AminSobati]

با تشکر از آقا امین و سایر دوستان
امین جان اگر امکان دارد یک مثال در این زمینه بزنید تا بنده هم بهتر متوجه بشوم.
با تشکر

پست چهارم رو ببینید. البته جا برای بهینه شدن داره هنوز. مثلا فیلد ID باید PK تعریف بشه تا ایندکس داشته باشه.

[amin_alexi]

من روی 11000 رکورد تست کردم :لبخند:
واسه حالتی شبیه دستور زیر 1 دقیقه و 41 ثانیه

Select Sum(Price)
From Tbl_Test
Where ID < T1.ID
) As Sum1و با استفاده از Cursor تنها 6 ثانیه !! :متعجب: (با Index گذاری روی جدول Temp)
:لبخند:

آمار جدید !!!
روی 171386 رکورد با روش اول تا نیم ساعت هم جواب نداد !!!!!! (قطعش کردم !!)
و با روش دوم 1 دقیقه و 30 ثانیه

:تشویق: "چیییی .... ایول ایول :لبخند:"

[SYNDROME]

راه بهتری هم هست.

ایشان میتوانند با استفاده از یک متغیر از نوع table که دارای دو ستون به نامهای ID, Sum است، در هر سطر ID و جمع سطرهای قبلی تا آنجا را ذخیره کنند. نه این که برای هر سطر مجبور باشند از اول جدول همه مقادیر را با هم جمع کنند
می توانید یک مثال اینجا قرار دهید تا دوستان هم استفاده کنند.
موفق باشید

[ar.shirazi]

چشم قربان

البته من الان روی این کامپیوتر دسترسی به sql server ندارم تا این کوئری را تست کنم اما ایده ام را اینجا مینویسم. در اولین فرصت آن را تست میکنم

با فرض این که ستون ID به صورت identity است، و فرض این که تابعی به نام myFunction داریم که مقدار ستون SumColumn را برای یک سطر قبل از این سطر فعلی بدست می آورد:



update myTable
set [SumColumn]= myFunc(ID) + [ValueColumn]


myFunc Function:

Select TOP(1) [SumColumn]
FROM myTable Where [ID]<@ID
order by ID desc
البته میتوان این دو کوئری را یکجا نوشت

ایده اصلی این عمل، استفاده از روش برنامه نویسی پویا است که در هر مرحله، به جای محاسبات از اول، از نتیجه محاسبات که ذخیره شده اند استفاده میکنیم

عیب راه حل پیشنهادی بنده این است که باید یک ستون به جدول اضافه کنیم که البته حجم چندانی نخواهد داشت اما کار را بسیار ساده میکند.
مثلا شما میتوانید این کوئری را محدود کنید:


update myTable
set [SumColumn]= myFunc(ID) + [ValueColumn]
where ID<20



موفق باشید

[amin_alexi]

سلام

نقل قول:
نوشته شده توسط ar.shirazi http://barnamenevis.org/forum/images/buttons/viewpost.gif (http://barnamenevis.org/forum/showthread.php?p=654375#post654375)
راه بهتری هم هست.

ایشان میتوانند با استفاده از یک متغیر از نوع table که دارای دو ستون به نامهای ID, Sum است، در هر سطر ID و جمع سطرهای قبلی تا آنجا را ذخیره کنند. نه این که برای هر سطر مجبور باشند از اول جدول همه مقادیر را با هم جمع کنند

می توانید یک مثال اینجا قرار دهید تا دوستان هم استفاده کنند.
موفق باشیدمن فکر میکنم این روش همانطور که قبلا هم گفتم همون روشیه که در Post 4 اومده با این تفاوت که Post 4 کامل تر و سریعتر از این روشه که در Post 11 گفته شده
در Post 11 باید یک Loop روی اطلاعات زده بشه تا این دستورات در تمام Recordها اعمال بشه و علت کندی نسبت به Post4 این Function می باشد !!

myFunc Function:

Select TOP(1) [SumColumn]
FROM myTable Where [ID]<@ID
order by ID desc که مقدار Sum اخر را با یک Select بدست می آورد ولی در صورتی که در Post 4 مقدار آخرین Sum در یک متغییر ذخیره شده ...

[تولائی]

سلام

سلام
من فکر میکنم این روش همانطور که قبلا هم گفتم همون روشیه که در Post 4 اومده با این تفاوت که Post 4 کامل تر و سریعتر از این روشه که در Post 11 گفته شده
در Post 11 باید یک Loop روی اطلاعات زده بشه تا این دستورات در تمام Recordها اعمال بشه و علت کندی نسبت به Post4 این Function می باشد !!

myFunc Function:

Select TOP(1) [SumColumn]
FROM myTable Where [ID]<@ID
order by ID desc که مقدار Sum اخر را با یک Select بدست می آورد ولی در صورتی که در Post 4 مقدار آخرین Sum در یک متغییر ذخیره شده ...
نه جان برادر این‌طور نیست. غرض این است که ستونی اضافه شود که شما sum را در آن نگهداری کنید. هنگامی که شما دارید سطری به سطور جدول‌تان اضافه می‌کنید این مقدار sum را هم در آن قرار دهید. و هر گاه سطری را update کردید فیلد sum سطر مربوطه به اضافه‌ی همه فیلدهای sum سطرهای بعد را هم update کنیم. اگر به این بگی افزونگی درست گفتی.



عیب راه حل پیشنهادی بنده این است که باید یک ستون به جدول اضافه کنیم که البته حجم چندانی نخواهد داشت اما کار را بسیار ساده میکند.
مثلا شما میتوانید این کوئری را محدود کنید:



update myTable
set [SumColumn]= myFunc(ID) + [ValueColumn]
where ID<20



موفق باشید
دوست عزیز این کار شما معایب بسیار دارد که در بالا درباره نگهداری‌یش صحبت شد. حال من این سئوال را دارم که شما فرض کرده‌اید که داده‌ها بر حسب Id مرتب شده‌اند ولی اگر من بخواهم که گزارش را بر حسب فیلد دیگری مرتب کنم چه باید کرد؟:متفکر:

[ar.shirazi]

سلام

نه جان برادر این‌طور نیست. غرض این است که ستونی اضافه شود که شما sum را در آن نگهداری کنید. هنگامی که شما دارید سطری به سطور جدول‌تان اضافه می‌کنید این مقدار sum را هم در آن قرار دهید. و هر گاه سطری را update کردید فیلد sum سطر مربوطه به اضافه‌ی همه فیلدهای sum سطرهای بعد را هم update کنیم. اگر به این بگی افزونگی درست گفتی.


دوست عزیز این کار شما معایب بسیار دارد که در بالا درباره نگهداری‌یش صحبت شد. حال من این سئوال را دارم که شما فرض کرده‌اید که داده‌ها بر حسب Id مرتب شده‌اند ولی اگر من بخواهم که گزارش را بر حسب فیلد دیگری مرتب کنم چه باید کرد؟:متفکر:
دوست گرامی
در خصوص جمله آخرتان این گونه عرض کنم:

من و شما در این مورد بسیار سخن گفته ایم. و نظر من هم در یک کلام این بوده که اگر افزونگی داده ها (بالاتر رفتن حجم داده های ذخیره شده) بتواند از بار پردازشی بکاهد، انجام آن مشکلی نخواهد داشت.

حال نظر شما چیز دیگری است . البته من به آن احترام میگذارم.

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