基于点数组的C# MongoDB排序

Question

使用最新的C# mongodb驱动程序和.NET 4.5.1。

我想在球员之间进行一些定制的比赛。假设我有以下模型。

public sealed class PlayerPoints
{
    [BsonId]
    public ObjectId PlayerId;

    public DateTime CreateDate;

    public int Points;
    public int[] SeasonalPoints;

}

我希望能够在特定的SeasonalPoints索引之间获得播放器/s的等级。

举个例子：

 {PlayerId : someId1, CreateDate : <someCreateDate>, Points : 1000, SeasonalPoints : [100,100,100,100,100,100,100,100,100,100,100]}
 {PlayerId : someId2, CreateDate : <someCreateDate>, Points : 1000, SeasonalPoints : [100,100,100,100,100,100,100,100,50,150,100]}
 {PlayerId : someId3, CreateDate : <someCreateDate>, Points : 1100, SeasonalPoints : [200,100,100,100,100,100,100,100,0,0,300]}

请注意这里有10个季节。我在搜索一个查询，它根据玩家的排名返回一个排序的列表。等级由提供的索引之间的点数之和来设置。

如果我在第9至第10季中查询排名，那么someId3是第一，someId2是后，someId1是最后。如果我在第7-9季查询排名，那么someId1是第一位，someId2是第二位，someId3是第三位。

我考虑过使用聚合，它将如何影响大约100万文档的性能，同时这个查询也将被频繁调用。

澄清

主要问题是如何构建将产生上述结果的查询，次要问题是查询将从服务器消耗多少性能。

谢谢。

Answer 1

至少，如果服务器所在的机器与数据库的机器不一样，您将获得更好的服务器性能。

另一方面，这可能意味着数据库机器可能“可用”较少，因为它忙于计算聚合结果。这是一种需要进行基准测试的东西，因为它因应用程序而异，而且有时也会有所不同。

这取决于用户负载、数据量、主机等。

至于查询，下面是我验证的实际工作的程序：

using System;
using System.Collections.Generic;
using System.Linq;
using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
using MongoDB.Driver;

namespace MongoAggregation
{

public sealed class PlayerPoints
{
    public ObjectId Id { get; set; }

    //Note that mongo addresses everything as UTC 0, so if you store local time zone values, make sure to use this attribute
    [BsonDateTimeOptions(Kind = DateTimeKind.Local)]
    public DateTime CreateDate { get; set; }

    public int Points { get; set; }
    //note that your model did not allow a player to not participate in some season, so I took the liberty of introducing a new sub document.
    //It is better to create sub documents that store metadata to make the query easier to implement
    public int[] SeasonalPoints { get; set; }
}

class Program
{

    static void Main(string[] args)
    {
        //used v 2.4.3 of C# driver and v 3.4.1 of the db engine for this example
        var client = new MongoClient();
        IMongoDatabase db = client.GetDatabase("agg_example");

        var collectionName = "points";
        db.DropCollection(collectionName);

        IMongoCollection<BsonDocument> collection = db.GetCollection<BsonDocument>(collectionName);
        IEnumerable<BsonDocument> data = GetDummyData().Select(d=>d.ToBsonDocument());

        collection.InsertMany(data);

        //some seasons to filter by - note transformation to zero based
        var seasons = new[] {6, 7};

        //This is the query body:
        var seasonIndex = seasons.Select(i => i - 1);

        //This shall remove all un-necessary seasons from aggregation pipeline
        var bsonFilter = new BsonDocument { new BsonElement("Season", new BsonDocument("$in", new BsonArray(seasonIndex))) };

        var groupBy = new BsonDocument// think of this as a grouping with an anonyous object declaration
        {
             new BsonElement("_id", "$_id"),//This denotes the key by which to group - in this case the player's id
             new BsonElement("playerSum", new BsonDocument("$sum", "$SeasonalPoints")),//We aggregate the player's points after unwinding the array
             new BsonElement("player", new BsonDocument("$first", "$$CURRENT")),// preserve player reference for projection stage
        };

        var sort = Builders<BsonDocument>.Sort.Descending(doc => doc["playerSum"]);

        var unwindOptions = new AggregateUnwindOptions<BsonDocument>
        {
            IncludeArrayIndex = new StringFieldDefinition<BsonDocument>("Season")
        };

        var projection = Builders<BsonDocument>.Projection.Expression((doc => doc["player"]));

        List<BsonValue> sorted = collection
            .Aggregate()
            .Unwind(x=>x["SeasonalPoints"], unwindOptions)
            .Match(bsonFilter)
            .Group(groupBy)
            .Sort(sort)
            .Project(projection)
            .ToList();

    }

    private static IEnumerable<PlayerPoints> GetDummyData()
    {
        return new[]
        {
            new PlayerPoints
            {
                CreateDate = DateTime.Today,
                SeasonalPoints = Enumerable.Repeat(100,7).ToArray()
            },
            new PlayerPoints
            {
                CreateDate = DateTime.Today,
                SeasonalPoints = new []
                {
                    100,100,100,100,100,150,100
                }
            },
            new PlayerPoints
            {
                CreateDate = DateTime.Today,
                SeasonalPoints = new []
                {
                    100,100,100,100,100,0,300
                }
            },
        };
    }
}
}

Answer 2

您可以使用3.4版本尝试下面的聚合。

聚集阶段- $project - $sort - $project.

数组聚合操作符- $reduce和$slice

算术运算符- $add

例子：

如果我在第九至第十季中查询排名，那么someId3是第一，someId2是后，someId1是最后

下面的代码将使用$project阶段来保持PlayerId和TotalPoints。`

TotalPoints使用$slice和SeasonalPoints数组，其起始位置为9，返回到2元素，然后是$reduce，它接受数组值，并将每个文档的值之和。

$sort阶段对TotalPoints值进行降序排序。

$project阶段输出PlayerId值。

class Program {
    static void Main(string[] args) {

        IMongoClient client = new MongoClient();
        IMongoDatabase db = client.GetDatabase("db");
        IMongoCollection < PlayerPoints > collection = db.GetCollection < PlayerPoints > ("collection");

        var pipeline = collection.Aggregate()
            .Project(p => new {
                PlayerId = p.PlayerId, TotalPoints = p.SeasonalPoints.Skip(9).Take(2).Aggregate((s1, s2) => s1 + s2)
            })
            .SortByDescending(s => s.TotalPoints)
            .Project(e => new {
                e.PlayerId
            });

        var result = pipeline.ToListAsync();

    }
}

Mongo Shell查询：

db.collection.aggregate([{
    "$project": {
        "PlayerId": "$_id",
        "TotalPoints": {
            "$reduce": {
                "input": {
                    "$slice": ["$SeasonalPoints", 9, 2]
                },
                "initialValue": 0,
                "in": {
                    "$add": ["$$value", "$$this"]
                }
            }
        },
        "_id": 0
    }
}, {
    "$sort": {
        "TotalPoints": -1
    }
}, {
    "$project": {
        "PlayerId": "$PlayerId",
        "_id": 0
    }
}])