All geek questions in one place

How to convert Sum over partition from T-SQL

I have the following table, data and T-SQL query.

create table sampledata 
(
    name nvarchar(50),
    sampletime datetime,
    samplevalue decimal
);

insert into sampledata (name, sampletime, samplevalue) values
('ABC1235', cast('2016/01/01 10:00:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 10:05:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 10:10:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 10:15:00 AM' as datetime), null),
('ABC1235', cast('2016/01/01 10:20:00 AM' as datetime), null),
('ABC1235', cast('2016/01/01 10:25:00 AM' as datetime), null),
('ABC1235', cast('2016/01/01 10:30:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 10:35:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 10:40:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 10:45:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 10:50:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 10:55:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 11:00:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 11:05:00 AM' as datetime), null),
('ABC1235', cast('2016/01/01 11:10:00 AM' as datetime), null),
('ABC1235', cast('2016/01/01 11:15:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 11:20:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 11:25:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 11:30:00 AM' as datetime), null),
('ABC1235', cast('2016/01/01 11:35:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 11:40:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 11:45:00 AM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 11:50:00 AM' as datetime), null),
('ABC1235', cast('2016/01/01 11:55:00 AM' as datetime), null),
('ABC1235', cast('2016/01/01 12:00:00 PM' as datetime), 50.00),
('ABC1235', cast('2016/01/01 12:05:00 PM' as datetime), null),
('ABC1235', cast('2016/01/01 12:10:00 PM' as datetime), null),
('ABC1235', cast('2016/01/01 12:15:00 PM' as datetime), null),
('ABC1235', cast('2016/01/01 12:20:00 PM' as datetime), null),
('ABC1235', cast('2016/01/01 12:25:00 PM' as datetime), null),
('ABC1235', cast('2016/01/01 12:30:00 PM' as datetime), null)
('ZYA4567', cast('2016/01/01 10:00:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 10:05:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 10:10:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 10:15:00 AM' as datetime), null),
('ZYA4567', cast('2016/01/01 10:20:00 AM' as datetime), null),
('ZYA4567', cast('2016/01/01 10:25:00 AM' as datetime), null),
('ZYA4567', cast('2016/01/01 10:30:00 AM' as datetime), null),
('ZYA4567', cast('2016/01/01 10:35:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 10:40:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 10:45:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 10:50:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 10:55:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 11:00:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 11:05:00 AM' as datetime), null),
('ZYA4567', cast('2016/01/01 11:10:00 AM' as datetime), null),
('ZYA4567', cast('2016/01/01 11:15:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 11:20:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 11:25:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 11:30:00 AM' as datetime), null),
('ZYA4567', cast('2016/01/01 11:35:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 11:40:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 11:45:00 AM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 11:50:00 AM' as datetime), null),
('ZYA4567', cast('2016/01/01 11:55:00 AM' as datetime), null),
('ZYA4567', cast('2016/01/01 12:00:00 PM' as datetime), 50.00),
('ZYA4567', cast('2016/01/01 12:05:00 PM' as datetime), null),
('ZYA4567', cast('2016/01/01 12:10:00 PM' as datetime), null),
('ZYA4567', cast('2016/01/01 12:15:00 PM' as datetime), null),
('ZYA4567', cast('2016/01/01 12:20:00 PM' as datetime), null),
('ZYA4567', cast('2016/01/01 12:25:00 PM' as datetime), 40.00),
('ZYA4567', cast('2016/01/01 12:30:00 PM' as datetime), 50.00)

Inquiry:

select 
    name, 
    sampletime,
    samplevalue,
sum(case when samplevalue is null then 0 else 1 end) 
over(partition by name order by sampletime) 
* case when samplevalue is null then 1 else 0 end as block
from sampledata

I need to convert it to LINQ.

I decided that I would break it into two stages (in my actual request, the associations are most present and where are the sentences than this example)

var list = (from s in db.sampledata).ToList();

Now i'm not sure how to do

sum(case when samplevalue is null then 0 else 1 end) 
over(partition by name order by sampletime) 
* case when samplevalue is null then 1 else 0 end as block
+4
source share
1 answer

You cannot do this with LinQToSQL, because there is no direct conversion to SQL over partitionfrom Linq.

But you can do it on the client side, I offer you this way:

int i = 0; //we need this for closure
var list = db.sampledata
    .OrderBy(x => x.name) 
    .ThenBy(x => x.sampletime) //order your data like partition function do
    .ToList() //get all from DB to server memory
    .Select(x =>
    {
        int block = 0; //logic of your block calculation
        if (x.samplevalue.HasValue)
            i++;
        else
            block = i;
        //return your full collection
        return new { 
                      name = x.name, 
                      sampletime = x.sampletime, 
                      samplevalue = x.samplevalue, 
                      block = block 
                   };
    });

, , block LinQ.

, , - T-SQL LinQToSQL DataContext, .

+2

Source: https://habr.com/ru/post/1652399/

More articles:


All Articles