在Sql Server中计算

我试图执行以下计算

样本数据:

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 不能只记录Nno.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)) )

我知道我们可以使用递归CTE来做到这一点,因为我不得不为超过500万条记录执行此操作.

我希望实现类似这种基于集合的方法

对于ccp: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  |
+-----+------+------+------+------+----------+-------------+

这个答案可能令人失望,但您可能会发现迭代CLR方法与任何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 StackoverflowQuestion41803909()
    {
        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();
        }
    }
};

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;