LINQ汇总30分钟至小时[英] LINQ aggregate 30 minute interval to hour

本文是小编为大家收集整理的关于LINQ汇总30分钟至小时的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我不是LINQ的超级专家,我在下面提供了第三方提供的数据: 数据

Start: 6:00
End: 6:30
value: 1 
Start: 7:00
End: 7:30
value: 1
Start: 8:00
End: 8:30
value: 1
Start: 9:00
End: 9:30
value: 1
Start: 10:00
End: 10:30
value: 1
Start: 11:00
End: 11:30
value: 1
Start: 12:00
End: 12:30
value: 1
Start: 13:00
End: 13:30
value: 1
Start: 14:00
End: 14:30
value: 1
...
Start: 05:00
End: 05:30
value: 1

此数据持续一周,然后30天和365天.

我需要将每个30分钟的块转换为一个小时.

例如

Start: 6:00
End: 7:00
Value: 2
Start:7:00
End: 8:00
Value:2
......

假设开始,结束和价值作为一排,有人可以帮助上述如何实现吗?

推荐答案

此查询能够按给定的AggregationType进行分组,并且能够使用第二个参数checkType进行过滤分组.

.
private enum AggerationType { Year = 1, Month = 2, Day = 3, Hour = 4 }

private IList<Data> RunQuery(AggerationType groupType, AggerationType checkType)
{
    // The actual query which does to trick
    var result =
        from d in testList
        group d by new {
            d.Start.Year,
            Month = (int)groupType >= (int)AggerationType.Month ? d.Start.Month : 1,
            Day = (int)groupType >= (int)AggerationType.Day ? d.Start.Day : 1,
            Hour = (int)groupType >= (int)AggerationType.Hour ? d.Start.Hour : 1
        } into g
        // The where clause checks how much data needs to be in the group
        where CheckAggregation(g.Count(), checkType)
        select new Data() { Start = g.Min(m => m.Start), End = g.Max(m => m.End), Value = g.Sum(m => m.Value) };

    return result.ToList();
}

private bool CheckAggregation(int groupCount, AggerationType checkType)
{
    int requiredCount = 1;
    switch(checkType)
    {
        // For year all data must be multiplied by 12 months
        case AggerationType.Year:
            requiredCount = requiredCount * 12; 
            goto case AggerationType.Month;
        // For months all data must be multiplied by days in month
        case AggerationType.Month:
            // I use 30 but this depends on the given month and year
            requiredCount = requiredCount * 30; 
            goto case AggerationType.Day;
        // For days all data need to be multiplied by 24 hour
        case AggerationType.Day:
            requiredCount = requiredCount * 24;
            goto case AggerationType.Hour;
        // For hours all data need to be multiplied by 2 (because slots of 30 minutes)
        case AggerationType.Hour:
            requiredCount = requiredCount * 2;
            break;

    }
    return groupCount == requiredCount;
}

在这里,如果需要:

class Data
{
    public DateTime Start { get; set; }
    public DateTime End { get; set; }
    public int Value { get; set; }
}

// Just setup some test data simulary to your example
IList<Data> testList = new List<Data>();
DateTime date = DateTime.Parse("6:00"); 

// This loop fills just some data over several years, months and days
for (int year = date.Year; year > 2010; year--)
{
    for(int month = date.Month; month > 0; month--)
    {
        for (int day = date.Day; day > 0; day--)
        {
            for(int hour = date.Hour; hour > 0; hour--)
            {
                DateTime testDate = date.AddHours(-hour).AddDays(-day).AddMonths(-month).AddYears(-(date.Year - year));
                testList.Add(new Data() { Start = testDate, End = testDate.AddMinutes(30), Value = 1 });
                testList.Add(new Data() { Start = testDate.AddMinutes(30), End = testDate.AddHours(1), Value = 1 });
            }
        }
    }
}

其他推荐答案

以下是代码.由于switch语句,这似乎有些丑陋.重构它会更好,但应该显示这个想法.

var items = input.Split('\n');

Func<string, string> f = s =>
{
    var strings = s.Split(new[] {':'}, 2);
    var key = strings[0];
    var value = strings[1];

    switch (key.ToLower())
    {
        case "start":
            return s;
        case "value":
            return String.Format("{0}: {1}", key, Int32.Parse(value) + 1);
        case "end": 
            return String.Format("{0}: {1:h:mm}", key,
                DateTime.Parse(value) +
                TimeSpan.FromMinutes(30));
        default:
            return "";
    }
};

var resultItems = items.Select(f);

Console.Out.WriteLine("result = {0}",
                          String.Join(Environment.NewLine, resultItems));

其他推荐答案

实际上很难用纯Linq完全对此进行处理.为了使生活更轻松,您需要编写至少一种助手方法,以使您可以改变枚举.看看下面的示例.在这里,我使用TimeInterval的IEnumerable,并具有自定义Split方法(用C#迭代器实现),该方法将两个元素加入一个Tuple:

class TimeInterval
{
    DateTime Start;
    DateTime End;
    int Value;
}

IEnumerable<TimeInterval> ToHourlyIntervals(
    IEnunumerable<TimeInterval> halfHourlyIntervals)
{
    return
        from pair in Split(halfHourlyIntervals)
        select new TimeInterval
        {
            Start = pair.Item1.Start,
            End = pair.Item2.End,
            Value = pair.Item1.Value + pair.Item2.Value
        };
}

static IEnumerable<Tuple<T, T>> Split<T>(
    IEnumerable<T> source)
{
    using (var enumerator = source.GetEnumerator())
    {
        while (enumerator.MoveNext())
        {
            T first = enumerator.Current;

            if (enumerator.MoveNext())
            {            
                T second = enumerator.Current;
                yield return Tuple.Create(first, second);
            }
        }
    }
}

可以将同样的应用程序应用于问题的第一部分(从字符串列表中提取半小时TimeInterval):

IEnumerable<TimeInterval> ToHalfHourlyIntervals(
    IEnumerable<string> inputLines)
{
    return
        from triple in TripleSplit(inputLines)
        select new TimeInterval
        {
            Start = DateTime.Parse(triple.Item1.Replace("Start: ", "")),
            End = DateTime.Parse(triple.Item2.Replace("End: ", "")),
            Value = Int32.Parse(triple.Item3)
        };
}

在这里,我使用返回a Tuple<T, T, T>的自定义TripleSplit方法(很容易编写).有了这个,完整的解决方案看起来像这样:

// Read data lazilzy from disk (or any other source)
var lines = File.ReadLines(path);

var halfHourlyIntervals = ToHalfHourlyIntervals(lines);

var hourlyIntervals = ToHourlyIntervals(halfHourlyIntervals);

foreach (var interval in hourlyIntervals)
{
    // process
}

这种解决方案的好处是它完全推迟了.它一次处理一行,这使您可以无限地处理大型资源,而不会出现任何不可记忆的例外的危险,考虑到您的给定要求,这似乎很重要:

此数据持续一周,然后30天和365天.

本文地址:https://www.itbaoku.cn/post/1557013.html

问题描述

I'm not a super expert on LINQ, I've a data below provided by third party: Data

Start: 6:00
End: 6:30
value: 1 
Start: 7:00
End: 7:30
value: 1
Start: 8:00
End: 8:30
value: 1
Start: 9:00
End: 9:30
value: 1
Start: 10:00
End: 10:30
value: 1
Start: 11:00
End: 11:30
value: 1
Start: 12:00
End: 12:30
value: 1
Start: 13:00
End: 13:30
value: 1
Start: 14:00
End: 14:30
value: 1
...
Start: 05:00
End: 05:30
value: 1

This data keeps going for a week then 30 days and 365days.

I need to transform each 30minute block in to an hour.

e.g

Start: 6:00
End: 7:00
Value: 2
Start:7:00
End: 8:00
Value:2
......

Assuming that Start, End and Value comes as one row, could someone help how above can be achieved?

推荐答案

This query is able to group by the given AggregationType and it is able to filter out incomplete groups using the second parameter checkType.

private enum AggerationType { Year = 1, Month = 2, Day = 3, Hour = 4 }

private IList<Data> RunQuery(AggerationType groupType, AggerationType checkType)
{
    // The actual query which does to trick
    var result =
        from d in testList
        group d by new {
            d.Start.Year,
            Month = (int)groupType >= (int)AggerationType.Month ? d.Start.Month : 1,
            Day = (int)groupType >= (int)AggerationType.Day ? d.Start.Day : 1,
            Hour = (int)groupType >= (int)AggerationType.Hour ? d.Start.Hour : 1
        } into g
        // The where clause checks how much data needs to be in the group
        where CheckAggregation(g.Count(), checkType)
        select new Data() { Start = g.Min(m => m.Start), End = g.Max(m => m.End), Value = g.Sum(m => m.Value) };

    return result.ToList();
}

private bool CheckAggregation(int groupCount, AggerationType checkType)
{
    int requiredCount = 1;
    switch(checkType)
    {
        // For year all data must be multiplied by 12 months
        case AggerationType.Year:
            requiredCount = requiredCount * 12; 
            goto case AggerationType.Month;
        // For months all data must be multiplied by days in month
        case AggerationType.Month:
            // I use 30 but this depends on the given month and year
            requiredCount = requiredCount * 30; 
            goto case AggerationType.Day;
        // For days all data need to be multiplied by 24 hour
        case AggerationType.Day:
            requiredCount = requiredCount * 24;
            goto case AggerationType.Hour;
        // For hours all data need to be multiplied by 2 (because slots of 30 minutes)
        case AggerationType.Hour:
            requiredCount = requiredCount * 2;
            break;

    }
    return groupCount == requiredCount;
}

Here some Test data if you want:

class Data
{
    public DateTime Start { get; set; }
    public DateTime End { get; set; }
    public int Value { get; set; }
}

// Just setup some test data simulary to your example
IList<Data> testList = new List<Data>();
DateTime date = DateTime.Parse("6:00"); 

// This loop fills just some data over several years, months and days
for (int year = date.Year; year > 2010; year--)
{
    for(int month = date.Month; month > 0; month--)
    {
        for (int day = date.Day; day > 0; day--)
        {
            for(int hour = date.Hour; hour > 0; hour--)
            {
                DateTime testDate = date.AddHours(-hour).AddDays(-day).AddMonths(-month).AddYears(-(date.Year - year));
                testList.Add(new Data() { Start = testDate, End = testDate.AddMinutes(30), Value = 1 });
                testList.Add(new Data() { Start = testDate.AddMinutes(30), End = testDate.AddHours(1), Value = 1 });
            }
        }
    }
}

其他推荐答案

Below is the code. It seems a little bit ugly because of switch statement. It would be better to refactor it but it should show the idea.

var items = input.Split('\n');

Func<string, string> f = s =>
{
    var strings = s.Split(new[] {':'}, 2);
    var key = strings[0];
    var value = strings[1];

    switch (key.ToLower())
    {
        case "start":
            return s;
        case "value":
            return String.Format("{0}: {1}", key, Int32.Parse(value) + 1);
        case "end": 
            return String.Format("{0}: {1:h:mm}", key,
                DateTime.Parse(value) +
                TimeSpan.FromMinutes(30));
        default:
            return "";
    }
};

var resultItems = items.Select(f);

Console.Out.WriteLine("result = {0}",
                          String.Join(Environment.NewLine, resultItems));

其他推荐答案

It's actually quite hard to completely approach this with with pure LINQ. To make life easier, you'll need to write atleast one helper method that allows you to transform an enumeration. Take a look at the example below. Here I make use of an IEnumerable of TimeInterval and have a custom Split method (implemented with C# iterators) that Joins two elements together in one Tuple:

class TimeInterval
{
    DateTime Start;
    DateTime End;
    int Value;
}

IEnumerable<TimeInterval> ToHourlyIntervals(
    IEnunumerable<TimeInterval> halfHourlyIntervals)
{
    return
        from pair in Split(halfHourlyIntervals)
        select new TimeInterval
        {
            Start = pair.Item1.Start,
            End = pair.Item2.End,
            Value = pair.Item1.Value + pair.Item2.Value
        };
}

static IEnumerable<Tuple<T, T>> Split<T>(
    IEnumerable<T> source)
{
    using (var enumerator = source.GetEnumerator())
    {
        while (enumerator.MoveNext())
        {
            T first = enumerator.Current;

            if (enumerator.MoveNext())
            {            
                T second = enumerator.Current;
                yield return Tuple.Create(first, second);
            }
        }
    }
}

The same can be applied to the first part of the problem (extracting half hourly TimeIntervals from the list of strings):

IEnumerable<TimeInterval> ToHalfHourlyIntervals(
    IEnumerable<string> inputLines)
{
    return
        from triple in TripleSplit(inputLines)
        select new TimeInterval
        {
            Start = DateTime.Parse(triple.Item1.Replace("Start: ", "")),
            End = DateTime.Parse(triple.Item2.Replace("End: ", "")),
            Value = Int32.Parse(triple.Item3)
        };
}

Here I make use of a custom TripleSplit method that returns a Tuple<T, T, T> (which will be easy to write). With this in place, the complete solution would look like this:

// Read data lazilzy from disk (or any other source)
var lines = File.ReadLines(path);

var halfHourlyIntervals = ToHalfHourlyIntervals(lines);

var hourlyIntervals = ToHourlyIntervals(halfHourlyIntervals);

foreach (var interval in hourlyIntervals)
{
    // process
}

What's nice about this solution is that it is completely deferred. It processes one line at a time, which allows you to process indefinately big sources without the danger of any out of memory exception, which seems important considering your given requirement:

This data keeps going for a week then 30 days and 365days.