SQL Server Analytical Toolkit: Using Windowing, Analytical, Ranking, and Aggregate Functions for Data and Statistical Analysis

Learn window function foundational concepts through a cookbook-style approach, beginning with an introduction to the OVER() clause, its various configurations in terms of how partitions and window frames are created, and how data is sorted in the partition so that the window function can operate on the partition data sets. You will build a toolkit based not only on the window functions but also on the performance tuning tools, use of Microsoft Excel to graph results, and future tools you can learn such as PowerBI, SSIS, and SSAS to enhance your data architecture skills.

This book goes beyond just showing how each function works. It presents four unique use-case scenarios (sales, financial, engineering, and inventory control) related to statistical analysis, data analysis, and BI. Each section is covered in three chapters, one chapter for each of the window aggregate, ranking, and analytical function categories.

Each chapter includes several TSQL code examples and is re-enforced with graphic output plus Microsoft Excel graphs created from the query output. SQL Server estimated query plans are generated and described so you can see how SQL Server processes the query. These together with IO, TIME, and PROFILE statistics are used to performance tune the query. You will know how to use indexes and when not to use indexes.

You will learn how to use techniques such as creating report tables, memory enhanced tables, and creating clustered indexes to enhance performance. And you will wrap up your learning with suggested steps related to business intelligence and its relevance to other Microsoft Tools such as Power BI and Analysis Services.

All code examples, including code to create and load each of the databases, are available online.

What You Will Learn

• Use SQL Server window functions in the context of statistical and data analysis
• Re-purpose code so it can be modified for your unique applications
• Study use-case scenarios that span four critical industries
• Get started with statistical data analysis and data mining using TSQL queries to dive deep into data
• Study discussions on statistics, how to use SSMS, SSAS, performance tuning, and TSQL queries using the OVER() clause.
• Follow prescriptive guidance on good coding standards to improve code legibility

Who This Book Is For

Intermediate to advanced SQL Server developers and data architects. Technical and savvy business analysts who need to apply sophisticated data analysis for their business users and clients will also benefit. This book offers critical tools and analysis techniques they can apply to their daily job in the disciplines of data mining, data engineering, and business intelligence.

這本書以食譜式的方式介紹了窗口函數的基礎概念，從介紹OVER()子句開始，詳細解釋了如何創建分區和窗口框架，以及如何對分區數據進行排序，以便窗口函數可以對分區數據集進行操作。您將建立一個工具包，不僅基於窗口函數，還包括性能調優工具、使用Microsoft Excel繪製結果以及未來可以學習的工具，如PowerBI、SSIS和SSAS，以提升您的數據架構技能。

這本書不僅展示了每個函數的工作原理，還提供了四個獨特的使用案例場景（銷售、財務、工程和庫存控制），涉及統計分析、數據分析和商業智能。每個章節都包含多個TSQL代碼示例，並通過圖形輸出和從查詢輸出創建的Microsoft Excel圖表進行強化。生成並描述了SQL Server的預估查詢計劃，以便您可以了解SQL Server如何處理查詢。這些與IO、TIME和PROFILE統計數據一起用於性能調優查詢。您將學會如何使用索引，以及何時不應使用索引。

您將學習如何使用技術，例如創建報表表格、增強內存表格和創建叢集索引以提高性能。最後，您將通過與商業智能以及其他Microsoft工具（如Power BI和Analysis Services）相關的建議步驟來結束學習。

所有代碼示例，包括創建和加載每個數據庫的代碼，都可以在線上找到。

您將學到什麼：
- 在統計和數據分析的背景下使用SQL Server窗口函數
- 重新使用代碼，以便對其進行修改以適應您的獨特應用程序
- 學習涵蓋四個關鍵行業的使用案例場景
- 使用TSQL查詢進行統計數據分析和數據挖掘，深入挖掘數據
- 學習有關統計學、如何使用SSMS、SSAS、性能調優和使用OVER()子句的TSQL查詢的討論
- 遵循有關良好編碼標準的指導，以提高代碼可讀性

適合閱讀對象：
中高級SQL Server開發人員和數據架構師。需要為其業務用戶和客戶應用複雜數據分析的技術和精明的業務分析師也會受益。本書提供了他們可以應用於日常工作中的關鍵工具和分析技術，涉及數據挖掘、數據工程和商業智能領域。

Angelo Bobak is a published author with more than four decades of experience and expertise in the areas of business intelligence, data architecture, data warehouse design, data modeling, master data management, and data quality using the Microsoft BI Stack across several industry sectors such as finance, publishing, and automotive. Before becoming a database architect, he was an electrical engineer in the power plant industry.

Angelo Bobak是一位有超過四十年經驗和專業知識的發表作家，專注於商業智能、數據架構、數據倉庫設計、數據建模、主數據管理和數據質量等領域，並使用Microsoft BI Stack在金融、出版和汽車等多個行業領域進行工作。在成為數據庫架構師之前，他曾是發電廠行業的電氣工程師。