[LINQ] GroupJoin

根據 MSDN 文章 - 執行左方外部聯結 的 GroupJoin 練習

namespace GroupJoinSample
{
    class Program
    {
        static void Main(string[] args)
        {
            var Data = DemoHelper.GetData();
            var People = Data.People;
            var Pets = Data.Pets;
            
            var LINQResult = People
                .GroupJoin(
                    Pets,
                    pe => pe,
                    pt => pt.Owner,
                    (personArg, petArg) => new { PersonProp = personArg, PetsProp = petArg });

            Console.WriteLine("----- LINQ GroupJoin 結果 -----");
            foreach (var person in LINQResult)
            {
                Console.WriteLine($"飼主：{person.PersonProp.FirstName} 有 {person.PetsProp.Count()} 隻寵物");

                foreach (var pet in person.PetsProp)
                {
                    Console.WriteLine($"--- 寵物名稱：{pet.Name}");
                }

                Console.WriteLine("");
            }

            Console.WriteLine("----- TSQL Left Join 結果 -----");

            var TSQLResult = People
                .GroupJoin(
                    Pets,
                    pe => pe,
                    pt => pt.Owner,
                    (personArg, petArg) => new { PersonProp = personArg, PetsProp = petArg })
                .SelectMany(
                    p => p.PetsProp.DefaultIfEmpty(), 
                    (pe, pt) => new {
                                        OwnerName = $"{pe.PersonProp.FirstName}",
                                        // 傳統寫法
                                        // PetName = pt == null ? "無寵物" : pt.Name
                                        // 語法糖寫法
                                        PetName = pt?.Name ?? "無寵物"
                                    });

            Console.WriteLine("飼主_寵物名字");
            foreach (var Record in TSQLResult)
            {
                Console.WriteLine(Record.OwnerName + "_" + Record.PetName);
            }

        }
    }

    public class DemoHelper
    {
        public static (List<Person> People, List<Pet> Pets) GetData()
        {
            Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
            Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

            Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
            Pet barley = new Pet { Name = "Barley", Owner = terry };
            Pet boots = new Pet { Name = "Boots", Owner = terry };
            Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };

            Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
            Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };

            // 該員沒有飼養寵物
            Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

            List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
            List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

            return (people, pets);
        }
    }

    public class Person
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public class Pet
    {
        public string Name { get; set; }
        public Person Owner { get; set; }
    }
}

參考資料
• Enumerable.GroupJoin Method
• 執行群組聯結 - 文章內有直接產生 XML 的 LINQ 作法
• 執行左方外部聯結
• [C#.NET][LINQ] Inner Join & Left Outer Join

[LINQ] SelectMany 應用 - Cross Join

利用 SelectMany 來達成 TSQL Cross Join 效果

namespace CrossJoinSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string[] Departments = { "研發" , "會計" , "人事" , "資訊" };
            string[] Employees = { "張三" , "李四" , "王五" };

            var result = Departments.SelectMany(
                e => Employees, 
                (d, e) => new {
                    Department = d ,
                    Employees = e
                });

            foreach (var item in result)
            {
                Console.WriteLine($"{item.Department} - {item.Employees}");
            }
        }
    }
}

延伸閱讀
• [LINQ] SelectMany

參考資料
• SelectMany (cross join)

[SQL] 分析函數應用 - 統計群組執行時間

在該 FB 社群內的問題，拿來練習視窗函數，問題為

在限定時間內，要統計每個群組起訖時間差，每一筆的下一筆為該資料的結束時間

原 PO 提供的測試資料

typeA 2018/09/01 12:00
typeB 2018/09/01 12:03
typeC 2018/09/01 12:08
typeB 2018/09/01 12:15
typeC 2018/09/01 12:22
typeA 2018/09/01 12:28

原 PO 提供的資料分析

要TypeA,TypeB,TypeC的累計時間噢~
TypeA:(5分鐘)
12:00~12:03=>3分鐘
12:28~12:30=>2分鐘
TypeB:(12分鐘)
12:03~12:08=>5分鐘
12:15~12:22=>7分鐘
TypeC:(13分鐘)
12:08~12:15=>7分鐘
12:22~12:28=>6分鐘

TSQL

-- 製作資料
DECLARE @Temp Table (TypeName char(5) , DataTime datetime)
INSERT INTO @Temp 
SELECT 'typeA' , '2018/09/01 12:00' UNION ALL
SELECT 'typeB' , '2018/09/01 12:03' UNION ALL
SELECT 'typeC' , '2018/09/01 12:08' UNION ALL
SELECT 'typeB' , '2018/09/01 12:15' UNION ALL
SELECT 'typeC' , '2018/09/01 12:22' UNION ALL
SELECT 'typeA' , '2018/09/01 12:28'

-- 實際執行
SELECT
  T.TypeName , 
  SUM(DATEDIFF(mi , StartTime , EndTime))
FROM
  (
    SELECT 
      TypeName ,
      DataTime AS StartTime ,
      -- 利用視窗函數 LEAD 來取得下一筆資料時間，為該筆資料結束時間
      -- 沒有下一筆資料的話，預設以 2018/09/01 12:30 為結束時間
      LEAD(DataTime, 1, '2018/09/01 12:30') OVER (ORDER BY DataTime) AS EndTime
    FROM @Temp
    WHERE DataTime BETWEEN '2018/09/01 12:00' AND '2018/09/01 12:30'
  ) AS T
GROUP BY T.TypeName

每筆資料起訖時間結果

最終群組統計結果

參考資料
• 如何計算各分類佔用分鐘數