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

[Nima_kyan]

سلام
دوستان من یه جدول با حجم اطلاعاتی بالا دارم (بیش از هفت میلیون رکورد) که یه سری اطلاعات رو میخوام ازش بیرون بکشم که با روشی که خودم انجام دادم محاسباتم بسیار زمان بر خواهد بود.حالا سراغ یه روش بهینه تر میگردم.
فیلدهای جدول:
شماره کارت (FKCard --> int)
زمان (DateTime -->Datetime)
اعتبار قبلی (PreEtebar -->int)
مبلغ (Price --> int)
اعتبار فعلی (RemEtebar -->int)

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

الگوریتم استفاده شده ی خودم:
گروه بندی بر اساس شماره کارت و مرتب سازی بر اساس فیلد زمان و اضافه کردن یه فیلد AutoNumber که به ازای هر کارت از یک شروع میشه و به تعداد کارت کشیدن اون نفر افزایش پیدا میکنه.(یعنی آخرین عدد هر کارت برابر با تعداد کارت کشیدنش)
حالا اومدم با استفاده از یه حلقه تودرتو هر رکورد یه کارت رو با رکورد بعدش مقایسه کردم و در صورت مغایرت فیلد اعتبارفعلی رکورد قبل با فیلد اعتبار قبلی رکورد فعلی مبلغ مغایرت رو محاسبه کرده و به یه جدول مجازی اضافه میکنم که میشه همون نتیجه ای که من میخوام.(ولی با یه هزینه زمانی بسیییار زیاد که به هیچ وجه منطقی نیست)

توضیح حلقه تودرتو:
-حلقه بیرونی به تعداد کارت های ثبت شده تکرار میشه.
-حلقه داخلی به تعداد کارت کشیدن هر کارت تکرار میشه.

مثال:
ردیف شماره کارت اعتبار قبلی مبلغ اعتبار فعلی تاریخ
1 1 1000 100 900 91/10/10 12:25:30
2 1 900 100 800 91/10/10 12:30:30
3 1 700 100 600 91/10/10 12:40:30

وقتی رکورد 2 و 3 رو باهم مقایسه کنیم متوجه میشیم که یک رکورد کاهش اعتبار 100 تومنی ارسال نشده بین این دو رکورد اتفاق افتاده ولی ارسال نشده.
حالا گزارشی که من گرفتم میگه بین این تاریخ و این تاریخ ، این مبلغ ارسال نشده.

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

[محمد سلیم آبادی]

سلام
من دقیق متوجه نشدم شما چه خروجی مد نظر دارین.
ولی تا اونجایی که فهمیدم. مساله خیلی سادس.
یک الگوریتم بهینه اینه که شما جدول را با خودش join کنید به شرطی که مبلغ باقیمانده سطر جاری مساوی نباشه با مبلغ انتقال یافته به سطر بعد به شرطی که دو سطر مربوط به یک شماره کارت باشد.
به زبان SQL
declare @t table
(row_id int, card_id int, value int, mines int, result int , date_time datetime)

insert into @t values
(1, 1, 1000, 100, 900, '2001-10-10 12:25:30'),
(2, 1, 900, 100, 800, '2001-10-10 12:30:30'),
(3, 1, 700, 100, 600, '2001-10-10 12:40:30');

select t1.row_id,
t1.date_time,
t2.date_time
from @t t1
join @t t2
on t1.card_id = t2.card_id
and t1.row_id = t2.row_id - 1
where t1.result > t2.value

[tooraj_azizi_1035]

نیما جان بردار.

[Arash_janusV3]

مشکلی که ابتدا به چشم می خورد ذخیره کردن محاسبات و یا همان مانده هستش
اعدادی که با محاسبه به دست میارید را ذخیره نکنید

[Nima_kyan]

یک الگوریتم بهینه اینه که شما جدول را با خودش join کنید به شرطی که مبلغ باقیمانده سطر جاری مساوی نباشه با مبلغ انتقال یافته به سطر بعد به شرطی که دو سطر مربوط به یک شماره کارت باشد.
[/SQL]

ممنون بابت کمکتون. این روش خیلی سریعتر از روش استفاده شده ی خودم هستش.


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

tooraj_azizi_1035 (http://barnamenevis.org/member.php?41757-tooraj_azizi_1035): تورج جان مرسی بابت وقتی که گذاشتی. روش ات رو تست کردم درست جواب میده ولی این روش هم مشکل کند بودن رو داره.

[tooraj_azizi_1035]

جواب با Join:

اگه جواب آقای msalim کارت رو راه میندازه برای ایشون Cost کمتری نسبت به کد من داره.
اگه اینطوره هر دو رو تو Management Studio کپی کن بعد انتخاب کن اجرا کن و Cost (Relative to the batch) رو برای هر دو مقایسه کن. مطمئناً برای من Cost بیشتری داره چون از MAX، MIN و SUM و GROUP BY استفاده کردم.


select table1.id,table1.cardno,table2.transdate,table1.tr ansdate, table2.remaining-table1.curcredit as diff from(
select id, cardno, transdate, curcredit,remaining from trans
where cardno in
(

select cardno from (select cardno,MAX(curcredit) as credit, SUM(price) as total, MIN(remaining) as remain from trans
group by cardno) t1
where t1.total+t1.remain<>t1.credit
) ) table1

inner join
(
select id+1 as id, cardno, transdate, curcredit,remaining from trans
where cardno in
(

select cardno from (select cardno,MAX(curcredit) as credit, SUM(price) as total, MIN(remaining) as remain from trans
group by cardno) t1
where t1.total+t1.remain<>t1.credit
)) table2 on table1.id=table2.id
where table2.remaining<>table1.curcredit and table1.cardno=table2.cardno

[محمد سلیم آبادی]

در صورت استفاده از نسخه 2012 می توانید این کوئری هم اجرا کنید که بهینه تر خواهد بود.
select row_id, date_time, t
from
(
select *,lead(value) over(partition by card_id order by row_id) as r,
lead(date_time) over(partition by card_id order by row_id) as t
from @t
)d
where r < result

[fakhravari]

داش سلیم اگر میشه یه تاپیک جدا برنید.
در مورد این select تو در تو کمی توضیح بدین.
در مورد )d که در select بالا است.
چنتا select دیده بودم که همچین چیزی به کار برده بودید/.
الحاق این گونه select ها از select بالا تر از خود؟

[Nima_kyan]

در صورت استفاده از نسخه 2012 می توانید این کوئری هم اجرا کنید که بهینه تر خواهد بود.
select row_id, date_time, t
from
(
select *,lead(value) over(partition by card_id order by row_id) as r,
lead(date_time) over(partition by card_id order by row_id) as t
from @t
)d
where r < result
ممنون:تشویق:
متاسفانه از نسخه 2008 استفاده میکنم و نمیتونم این رو تست کنم. ولی این خیلی واسم جالب بود.(استفاده کردن از تابع lead و دسترسی به رکورد های بعدی)
یه سوال: اونجوری که من متوجه شدم این تابع (lead) فقط واسه دسترسی به رکوردهای بعد رکورد جاری میتونه باشه.درسته؟یعنی نمیشه به رکوردهای قبلیش دسترسی داشت(offset اش فقط باید مثبت باشه)؟


در مورد این select تو در تو کمی توضیح بدین.
در مورد )d که در select بالا است.
چنتا select دیده بودم که همچین چیزی به کار برده بودید/.
الحاق این گونه select ها از select بالا تر از خود؟
آقای فخراوری کد select بالا یه select تودرتوی معمولیه.کجای قضیه گنگه؟

[محمد سلیم آبادی]

تابع lag هم برای سطر قبلی بکار میره.
http://msdn.microsoft.com/en-us/library/hh231256.aspx