Filter on properties outside of the group by?

[loraderon]

I have statistics stored in raven such as:

{
    "Date": "2023-07-20",
    "CampaignId": "329bb91c-b126-4e44-986a-9e6934221d0a",
    "Media": "example.com",
    "Impressions": 21,
}

And I would like to answer the question; "what are the top 10 medias for the following campaigns between these dates?".
My problem is that I cannot group by only Media since I have to filter on CampaignId and Date.

I know about the OLAP ETL Task, but I would really like to not involve another database for this query.

This i how I would achive it in postgresql.

SELECT media, sum(impressions)
FROM statistics
WHERE 
 campaignid = '329bb91c-b126-4e44-986a-9e6934221d0a'
AND
 date BETWEEN '2023-01-01' AND '2023-10-01'
GROUP BY media
ORDER BY sum(impressions) DESC
LIMIT 10

[garayx]

Hi

You can create a map reduce index
https://ravendb.net/docs/article-page/6.0/Csharp/indexes/map-reduce-indexes#indexes-map-reduce-indexes
with CampaignId, Media keys, and aggregation on Impressions and Date fields
then query the index

[loraderon]

Could you elaborate on your proposed solution?

I'm trying with the following query and index but I'm getting an error that Impressions are not indexed so I cannot sort on it.

The field 'Impressions' is not indexed in 'TopMediaIndex', cannot query/sort on fields that are not indexed in query: from index 'TopMediaIndex' as x where x.CampaignId = $p0 order by Impressions as double desc select { Media : x.Media, Impressions : x.Data.filter(i=>compareDates(i.Date, $p1, 'GreaterThanOrEqual')&&compareDates(i.Date, $p2, 'LessThanOrEqual')).map(i=>i.Impressions).reduce((a, b) => a + b, 0) } limit $p3, $p4

var campaignId = Guid.Parse("329bb91c-b126-4e44-986a-9e6934221d0a");
var start = new DateOnly(2023, 1, 1);
var end = new DateOnly(2023, 10, 1);
var results = session.Query<TopMediaIndex.Result, TopMediaIndex>()
	.Where(x => x.CampaignId == campaignId)
	.Select(x => new
	{
		x.Media,
		Impressions = x.Data
			.Where(i => i.Date >= start && i.Date <= end)
			.Sum(i => i.Impressions)
	})
	.OrderByDescending(m => m.Impressions)
	.Take(10)
	.ToArray();

results.Dump();

public class TopMediaIndex : AbstractIndexCreationTask<Statistics, TopMediaIndex.Result>
{
	public TopMediaIndex()
	{
		Map = stats => 
			from s in stats
			group s by new { s.CampaignId, s.Media, } into g
			select new Result {
				CampaignId = g.Key.CampaignId,
				Media = g.Key.Media,
				Data = g
					.Select(x => new DateImpressions { 
						Date = x.Date, 
						Impressions = x.Impressions, 
					})
					.ToArray(),
			};
			
		Reduce = results =>
			from result in results
			group result by new { 
				result.CampaignId, 
				result.Media, 
			} 
			into g
			select new Result {
				CampaignId = g.Key.CampaignId,
				Media = g.Key.Media,
				Data = g
					.SelectMany(x=>x.Data)
					.ToArray()
			};

	}	
	
	public class DateImpressions
	{
		public DateOnly Date {get; set; }
		public double Impressions {get; set; }
	}
	
	public class Result
	{
		public Guid CampaignId { get; set; }
		public string Media { get; set; }
		public DateImpressions[] Data { get; set; }
	}
}

[loraderon]

I created a very small sample here http://live-test.ravendb.net/studio/index.html#databases/indexes/edit/Media?&database=stats

[loraderon]

I kinda fixed my issues by including the TotalImpressions in the index and selecting more than limit of 10 and then finally massaging the response in the memory

var campaignId = Guid.Parse("329bb91c-b126-4e44-986a-9e6934221d0a");
var start = new DateOnly(2023, 1, 1);
var end = new DateOnly(2023, 10, 1);

var response = session.Query<Statistics_Media.Result, Statistics_Media>()
	.Where(x => x.CampaignId == campaignId)
	.OrderByDescending(m => m.TotalImpressions)
	.Take(10 * 10) // taking more than required to hopefully include all correct values after applying the date filtering in-memory
	.ToArray();

var results = response // aggregate and filter the response in-memory
	.GroupBy(x => x.Media)
	.Select(g => new
	{
		Media = g.Key,
		Impressions = g.SelectMany(x => x.Data)
			.Where(day => day.Date >= start && day.Date <= end)
			.Sum(day => day.Impressions),
		Clicks = g.SelectMany(x => x.Data)
			.Where(day => day.Date >= start && day.Date <= end)
			.Sum(day => day.Clicks),
	})
	.OrderByDescending(x => x.Impressions)
	.Take(10) // taking the actual wanted number of results
	.ToArray();

Updated index:

public class Statistics_Media : AbstractIndexCreationTask<DailyDetailedStatistics, Statistics_Media.Result>
{
    public Statistics_Media()
    {
        Map = stats =>
            from s in stats
            group s by new { s.CampaignId, s.Media, } into g
            select new Result
            {
                CampaignId = g.Key.CampaignId,
                Media = g.Key.Media,
                TotalImpressions = g.Sum(x => x.Impressions),
                Data = g
                    .Select(x => new DateStat
                    {
                        Date = x.Date,
                        Impressions = x.Impressions,
                        Clicks = x.Clicks,
                    })
                    .ToArray(),
            };

        Reduce = results =>
            from result in results
            group result by new
            {
                result.CampaignId,
                result.Media,
            }
            into g
            select new Result
            {
                CampaignId = g.Key.CampaignId,
                Media = g.Key.Media,
                TotalImpressions = g.Sum(x => x.TotalImpressions),
                Data = g
                    .SelectMany(x => x.Data)
                    .ToArray()
            };

        Index(x => x.TotalImpressions, FieldIndexing.Default);
    }

    public class DateStat
    {
        public required DateOnly Date { get; set; }
        public required double Impressions { get; set; }
        public required double Clicks { get; set; }
    }

    public class Result
    {
        public required Guid CampaignId { get; set; }
        public required string Media { get; set; }
        public required double TotalImpressions { get; set; }
        public required DateStat[] Data { get; set; }
    }
}

[garayx]

seems like doing the aggregation in memory is the way to go, I come up with a solution like this

1. filter the requested campaign and date range
2. group by the media and sum of impressions
3. order by the sum

                    var filterResults = session.Query<Statistics>()
                        .Where(x => x.CampaignId == campaignId)
                        .Where(x => x.Date >= start && x.Date <= end)
                        .Select(x => new { Media = x.Media, Impressions = x.Impressions })
                        .ToList();

                    var results = filterResults.GroupBy(x => new { x.Media })
                        .Select(x => new { Name = x.Key.Media, Sum = x.Sum(u => u.Impressions) }).OrderByDescending(x=>x.Sum)
                        .Take(15)
                        .ToList();

[loraderon]

seems like doing the aggregation in memory is the way to go

I was hoping that I was wrong here, is there no way at all to aggregate after querying the index?