ROW_NUMBER() OVER (PARTITION BY yy ORDER BY zz) in Linq

　　OVER PARTITION BY在oracle分析数据中经常用到的函数，Min Max也会在分析中经常使用，总结一下在Linq中的用法如下

 

       static void Main(string[] args)
        {
            var beatles = (new[] { new { id=1 , inst = "guitar" , name="john" },
                new { id=2 , inst = "guitar" , name="george" },
                new { id=3 , inst = "guitar" , name="paul" },
                new { id=4 , inst = "drums" , name="ringo" },
                new { id=5 , inst = "drums" , name="pete" }
                });
            var rnb = beatles
                    .OrderBy(x => x.id) // order by yyy
                    .GroupBy(x => x.inst)   // partition by xxx
                    .Select(group => new { Group = group, Count = group.Count() })
                    .SelectMany(e => e.Group.Select(v => v)
                        .Zip(Enumerable.Range(1, e.Count) /*ROW_NUMBER*/, (i, j) => new {
                            i.inst,
                            i.name,
                            rn = j
                        })
                    );

            var min = beatles.OrderBy(x => x.id)
                        .GroupBy(x => x.inst)
                        .Select(group => new { Group = group, Minid = group.Min(e => e.id) })
                        .SelectMany(g => g.Group.Select(b => new {
                            b.id,
                            b.inst,
                            b.name,
                            minid = g.Minid
                        }));

            foreach (var item in rnb) // min
            {
                Console.WriteLine("{0}|{1}|{2}", item.inst, item.name, item.rn);
                //Console.WriteLine("{0}|{1}|{2}", item.inst, item.name, item.minid);
            }

            Console.ReadKey();
        }