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




sql-server tsql (4)

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

नमूना डेटा:

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) विंडो एग्रीगेट फ़ंक्शन का उपयोग करके।


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

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  |
+-----+------+------+------+------+----------+-------------+

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

;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

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


कुछ समय के लिए इसके साथ खेलने के बाद, मुझे विश्वास है कि इस प्रश्न के उत्तर में कि क्या यह 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 में भी अनुमति नहीं है:

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

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


यह उत्तर निराशाजनक हो सकता है, लेकिन आपको यह पता चलेगा कि एक पुनरावृत्ति सीएलआर दृष्टिकोण किसी भी TSQL दृष्टिकोण के साथ प्रतिस्पर्धात्मक रूप से करता है।

निम्नलिखित प्रयास करें ( अभी तक फिर से चल रहे रकम के आधार पर : SQLCLR दिन बचाता है! )

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void Question41803909()
    {
        using (SqlConnection conn = new SqlConnection("context connection=true;"))
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = @"
SELECT [rno],
       [ccp],
       [col1],
       [col2],
       [col3],
       [col4]
FROM   Table1
ORDER  BY ccp,
          rno 
";

            SqlMetaData[] columns = new SqlMetaData[7];
            columns[0] = new SqlMetaData("rno", SqlDbType.Int);
            columns[1] = new SqlMetaData("ccp", SqlDbType.VarChar, 50);
            columns[2] = new SqlMetaData("col1", SqlDbType.Int);
            columns[3] = new SqlMetaData("col2", SqlDbType.Int);
            columns[4] = new SqlMetaData("col3", SqlDbType.Int);
            columns[5] = new SqlMetaData("col4", SqlDbType.Decimal, 17, 6);
            columns[6] = new SqlMetaData("result", SqlDbType.Decimal, 17, 6);

            SqlDataRecord record = new SqlDataRecord(columns);

            SqlContext.Pipe.SendResultsStart(record);

            conn.Open();

            SqlDataReader reader = comm.ExecuteReader();

            string prevCcp = null;
            decimal offset = 0;

            while (reader.Read())
            {
                string ccp = (string)reader[1];
                int col1 = (int)reader[2];
                int col3 = (int)reader[4];
                decimal col4 = (decimal)reader[5];

                if (prevCcp != ccp)
                {
                    offset = 0;
                }

                offset = ((col1 + offset) * (1 + col4));
                record.SetInt32(0, (int)reader[0]);
                record.SetString(1, ccp);
                record.SetInt32(2, col1);
                record.SetInt32(3, (int)reader[3]);
                record.SetInt32(4, col3);
                record.SetDecimal(5, col4);
                record.SetDecimal(6, col3 - offset);

                SqlContext.Pipe.SendResultsRow(record);

                prevCcp = ccp;
            }

            SqlContext.Pipe.SendResultsEnd();
        }
    }
};





sql-server-2012