sql-server - Sql सर्वर में गणना




tsql sql-server-2012 (6)

self join एप्रोच के साथ एप्रोच। यकीन नहीं है कि यह cross apply संस्करण के साथ आपके संस्करण की तुलना में अधिक कुशल होगा।

WITH T AS
  (SELECT *,
          ROW_NUMBER() OVER(PARTITION BY CCP
                            ORDER BY RNO) AS RN
   FROM #TABLE1)
SELECT T1.RNO,
       T1.CCP,
       T1.COL1,
       T1.COL2,
       T1.COL3,
       T1.COL3-SUM(T2.COL1*POWER(1+T1.COL2/100.0,T1.RN-T2.RN+1)) AS RES
FROM T T1
JOIN T T2 ON T1.CCP=T2.CCP
AND T1.RN>=T2.RN
GROUP BY T1.RNO,
         T1.CCP,
         T1.COL1,
         T1.COL2,
         T1.COL3

Sample Demo

मैं निम्नलिखित गणना करने की कोशिश कर रहा हूं

नमूना डेटा:

CREATE TABLE #Table1
  (
     rno   int identity(1,1),
     ccp   varchar(50),
     [col1] INT,
     [col2] INT,
     [col3] INT,
     col4 as [col2]/100.0
  );

INSERT INTO #Table1
            (ccp,[col1],[col2],[col3])
VALUES      ('ccp1',15,10,1100),
            ('ccp1',20,10,1210),
            ('ccp1',30,10,1331),
            ('ccp2',10,15,900),
            ('ccp2',15,15,1000),
            ('ccp2',20,15,1010)

+-----+------+------+------+------+----------+
| rno | ccp  | col1 | col2 | col3 |   col4   |
+-----+------+------+------+------+----------+
|   1 | ccp1 |   15 |   10 | 1100 | 0.100000 |
|   2 | ccp1 |   20 |   10 | 1210 | 0.100000 |
|   3 | ccp1 |   30 |   10 | 1331 | 0.100000 |
|   4 | ccp2 |   10 |   15 |  900 | 0.150000 |
|   5 | ccp2 |   15 |   15 | 1000 | 0.150000 |
|   6 | ccp2 |   20 |   15 | 1010 | 0.150000 |
+-----+------+------+------+------+----------+

नोट: यह सिर्फ 3 रिकॉर्ड नहीं है प्रत्येक ccp में N no.of रिकॉर्ड हो सकता है

अपेक्षित परिणाम :

1083.500000 --1100 - (15 * (1+0.100000))
1169.850000 --1210 - ((20 * (1+0.100000)) + (15 * (1+0.100000)* (1+0.100000)) )
1253.835000 --1331 - ((30 * (1+0.100000)) + (20 * (1+0.100000)* (1+0.100000)) + (15 * (1+0.100000)* (1+0.100000) *(1+0.100000)) )
888.500000  --900 - (10 * (1+0.150000))
969.525000  --1000 - ((15 * (1+0.150000)) + (10 * (1+0.150000)* (1+0.150000)) )
951.953750  --1010 - ((20 * (1+0.150000)) + (15 * (1+0.150000)* (1+0.150000)) + (10 * (1+0.150000)* (1+0.150000) *(1+0.150000)) )

मुझे पता है कि हम Recursive CTE का उपयोग करके ऐसा कर सकते हैं, यह कुशल नहीं है क्योंकि मुझे इसे 5 मिलियन से अधिक रिकॉर्ड के लिए करना है।

मैं इस सेट आधारित दृष्टिकोण की तरह कुछ लागू करने के लिए देख रहा हूँ

ccp1 लिए : ccp1

SELECT col3 - ( col1 * ( 1 + col4 ) )
FROM   #Table1
WHERE  rno = 1

SELECT rno,
       col3 - ( ( col1 * Power(( 1 + col4 ), 1) ) + ( Lag(col1, 1)
                                                        OVER(
                                                          ORDER BY rno ) * Power(( 1 + col4 ), 2) ) )
FROM   #Table1
WHERE  rno IN ( 1, 2 )

SELECT rno,
       col3 - ( ( col1 * Power(( 1 + col4 ), 1) ) + ( Lag(col1, 1)
                                                        OVER(
                                                          ORDER BY rno ) * Power(( 1 + col4 ), 2) ) + ( Lag(col1, 2)
                                                                                                          OVER(
                                                                                                            ORDER BY rno ) * Power(( 1 + col4 ), 3) ) )
FROM   #Table1
WHERE  rno IN ( 1, 2, 3 ) 

क्या एकल क्वेरी में गणना करने का कोई तरीका है?

अद्यतन करें :

फिर भी सुझाव के लिए खुला हूं। मैं दृढ़ता से निहारता हूं कि ऐसा कुछ होना चाहिए SUM () Over(Order by) विंडो एग्रीगेट फ़ंक्शन का उपयोग करके।


एक अन्य विकल्प

CREATE TABLE #Table1
  (
     rno   int identity(1,1),
     ccp   varchar(50),
     [col1] INT,
     [col2] INT,
     [col3] INT,
     col4 as [col2]/100.0
  );

INSERT INTO #Table1
            (ccp,[col1],[col2],[col3])
VALUES      ('ccp1',15,10,1100),
            ('ccp1',20,10,1210),
            ('ccp1',30,10,1331),
            ('ccp1',40,10,1331),
            ('ccp2',10,15,900),
            ('ccp2',15,15,1000),
            ('ccp2',20,15,1010);

select t.*, col3-s
from(
    select *, rn = row_number() over(partition by ccp order by rno)
    from #Table1
) t
cross apply (
    select s=sum(pwr*col1)
    from(
        select top(rn)
           col1, pwr = power(1+col4, rn + 1 - row_number() over(order by rno))
        from #Table1 t2
        where t2.ccp=t.ccp
        order by row_number() over(order by rno)
        )t3
    )t4
order by rno;

कुछ समय के लिए इसके साथ खेलने के बाद, मुझे विश्वास है कि इस प्रश्न के उत्तर में कि क्या यह sum() over (order by) साथ किया जा सकता है नहीं। यह कोड उतना ही करीब है जितना मुझे मिल सकता है:

select  *, col3 - sum(col1 * power(1 + col4, row_num)) over (partition by ccp order by col1)
from    (
        select  *, row_number() over (partition by ccp order by rno asc) row_num
        from    @Table1
        ) a
order   by 1,2;

यह प्रत्येक ccp समूह में पहली पंक्ति के लिए सही परिणाम लौटाएगा। इसके बजाय rno desc का उपयोग करके row_num की गणना करके फिर प्रत्येक rno desc में अंतिम पंक्ति सही होगी।

ऐसा प्रतीत होता है कि इसे सरल तरीके से काम करने का एकमात्र तरीका है जो वाक्यविन्यास बताता है:

  1. फ़ंक्शन पर कुल के भीतर वास्तविक पंक्ति को संदर्भित करने के लिए सिंटैक्स समर्थन। यह टी-एसक्यूएल में मौजूद है जहां तक ​​मैं पा सकता हूं।
  2. विंडो फ़ंक्शन के भीतर विंडो फ़ंक्शन के लिए सिंटैक्स समर्थन। यह निम्न त्रुटि के अनुसार T-SQL में भी अनुमति नहीं है:

विंडो किए गए फ़ंक्शन का उपयोग किसी अन्य विंडो फ़ंक्शन या एग्रीगेट के संदर्भ में नहीं किया जा सकता है।

यह एक दिलचस्प समस्या थी। मुझे उत्सुकता होगी कि वास्तविक समाधान गलत होने के बावजूद यह समाधान आपके बड़े डेटासेट के खिलाफ कैसा प्रदर्शन करता है।


इसे इस्तेमाल करे:

;with 
    val as (
        select 
            *, 
            (1 + col2 / 100.00) val,
            row_number() over(partition by ccp order by rno desc) rn
        from #Table1),
res as (
        select 
            v1.rno, 
            --min(v1.ccp) ccp,
            --min(v1.col1) col1, 
            --min(v1.col2) col2, 
            min(v1.col3) col3, 
            sum(v2.col1 * power(v2.val, 1 + v2.rn - v1.rn)) sum_val
        from val v1
        left join val v2 on v2.ccp = v1.ccp and v2.rno <= v1.rno
        group by v1.rno)
select *, col3 - isnull(sum_val, 0)
from res

लेकिन प्रदर्शन सूचकांक पर निर्भर करता है। विवरण के लिए अनुक्रमणिका संरचना। सर्वश्रेष्ठ प्रदर्शन तब प्राप्त किया जा सकता है जब आप इसे अधिक अस्थायी तालिकाओं में विभाजित करेंगे।


अंत में मैंने नीचे के दृष्टिकोण का उपयोग करके परिणाम प्राप्त किया

SELECT a.*,
       col3 - res AS Result
FROM   #TABLE1 a
       CROSS apply (SELECT Sum(b.col1 * Power(( 1 + b.COL2 / 100.00 ), new_rn)) AS res
                    FROM   (SELECT Row_number()
                                     OVER(
                                       partition BY ccp
                                       ORDER BY rno DESC) new_rn,*
                            FROM   #TABLE1 b
                            WHERE  a.ccp = b.ccp
                                   AND a.rno >= b.rno)b) cs

परिणाम :

+-----+------+------+------+------+----------+-------------+
| rno | ccp  | col1 | col2 | col3 |   col4   |   Result    |
+-----+------+------+------+------+----------+-------------+
|   1 | ccp1 |   15 |   10 | 1100 | 0.100000 | 1083.500000 |
|   2 | ccp1 |   20 |   10 | 1210 | 0.100000 | 1169.850000 |
|   3 | ccp1 |   30 |   10 | 1331 | 0.100000 | 1253.835000 |
|   4 | ccp2 |   10 |   15 |  900 | 0.150000 | 888.500000  |
|   5 | ccp2 |   15 |   15 | 1000 | 0.150000 | 969.525000  |
|   6 | ccp2 |   20 |   15 | 1010 | 0.150000 | 951.953750  |
+-----+------+------+------+------+----------+-------------+





sql sql-server tsql sql-server-2012