添加便签

笔记

Section 1 / 4

计数统计

了解计数模式（Tally count pattern），用于识别数据集中的值频率，通过SQL聚合帮助获得洞察并支持决策。

导入

计数统计

了解计数模式（Tally count pattern），用于识别数据集中的值频率，通过SQL聚合帮助获得洞察并支持决策。

计数统计模式的语法

1SELECT    要分组的列,
2          COUNT(*) AS 计数
3FROM      表名
4[WHERE    条件]
5GROUP BY  要分组的列
6[ORDER BY 计数 DESC];

字段	类型
OrderID	int
CustomerID	int
OrderDate	date
TotalAmount	decimal(10,2)
ShippedDate	date
DeliveryStatus	enum('Pending','Shipped','Delivered','Cancelled')
EmployeeID	int
CreatedAt	timestamp
ExpectedDeliveryDate	date
ActualDeliveryDate	date
LateDelivery	tinyint(1)
PaymentMethod	enum('Credit Card','PayPal','Cash on Delivery')
ReturnCount	int
FraudRisk	tinyint(1)

1SELECT   CustomerID,
2         COUNT(*) AS TotalOrders
3FROM     Orders
4GROUP BY CustomerID
5ORDER BY TotalOrders DESC;

字段	类型
OrderDetailID	int
OrderID	int
ProductID	int
Quantity	int
TotalItemPrice	decimal(10,2)

1SELECT   ProductID,
2         COUNT(*) AS TimesOrdered
3FROM     Order_Details
4GROUP BY ProductID
5ORDER BY TimesOrdered DESC;

字段	类型
OrderID	int
CustomerID	int
OrderDate	date
TotalAmount	decimal(10,2)
ShippedDate	date
DeliveryStatus	enum('Pending','Shipped','Delivered','Cancelled')
EmployeeID	int
CreatedAt	timestamp
ExpectedDeliveryDate	date
ActualDeliveryDate	date
LateDelivery	tinyint(1)
PaymentMethod	enum('Credit Card','PayPal','Cash on Delivery')
ReturnCount	int
FraudRisk	tinyint(1)

1SELECT   OrderDate,
2         COUNT(*) AS OrdersPerDay
3FROM     Orders
4GROUP BY OrderDate
5ORDER BY OrderDate;

字段	类型
CustomerID	int
CustomerName	varchar(50)
Email	varchar(50)
Phone	varchar(15)
Address	varchar(100)
LastLogin	date
CreatedAt	timestamp
CustomerTier	enum('New','Regular','VIP')
ReferralID	int
LoyaltyPoints	int
LastPurchaseDate	date
IsChurnRisk	tinyint(1)

1SELECT   CustomerTier,
2         COUNT(*) AS NumCustomers
3FROM     Customers
4GROUP BY CustomerTier;

字段	类型
ProductID	int
ProductName	varchar(50)
CategoryID	int
Price	decimal(10,2)
Stock	int
LastRestockDate	date
MonthlySales	int
InventoryTurnoverRate	decimal(5,2)

字段	类型
CategoryID	int
CategoryName	varchar(50)

1SELECT   c.CategoryName,
2         COUNT(p.ProductID) AS ProductsInCategory
3FROM     Products p
4JOIN     Categories c ON p.CategoryID = c.CategoryID
5GROUP BY c.CategoryName
6ORDER BY ProductsInCategory DESC;

字段	类型
CustomerID	int
CustomerName	varchar(50)
Email	varchar(50)
Phone	varchar(15)
Address	varchar(100)
LastLogin	date
CreatedAt	timestamp
CustomerTier	enum('New','Regular','VIP')
ReferralID	int
LoyaltyPoints	int
LastPurchaseDate	date
IsChurnRisk	tinyint(1)

CustomerTier	Total
Regular	12
VIP	8

1-- 写你的查询语句

1SELECT   CustomerTier, 
2         COUNT(*) AS Total
3FROM     Customers
4GROUP BY CustomerTier;

1SELECT   CustomerTier, 
2         COUNT(*) AS Total
3FROM     Customers
4GROUP BY CustomerTier;

字段	类型
OrderID	int
CustomerID	int
OrderDate	date
TotalAmount	decimal(10,2)
ShippedDate	date
DeliveryStatus	enum('Pending','Shipped','Delivered','Cancelled')
EmployeeID	int
CreatedAt	timestamp
ExpectedDeliveryDate	date
ActualDeliveryDate	date
LateDelivery	tinyint(1)
PaymentMethod	enum('Credit Card','PayPal','Cash on Delivery')
ReturnCount	int
FraudRisk	tinyint(1)

DeliveryStatus	OrderCount
Shipped	12
Delivered	33

1-- 写你的查询语句

1SELECT   DeliveryStatus, 
2         COUNT(*) AS OrderCount
3FROM     Orders
4GROUP BY DeliveryStatus;

1SELECT   DeliveryStatus, 
2         COUNT(*) AS OrderCount
3FROM     Orders
4GROUP BY DeliveryStatus;

字段	类型
ProductID	int
ProductName	varchar(50)
CategoryID	int
Price	decimal(10,2)
Stock	int
LastRestockDate	date
MonthlySales	int
InventoryTurnoverRate	decimal(5,2)

CategoryID	ProductCount
1	7
2	3

1-- 写你的查询语句

1SELECT   CategoryID, 
2         COUNT(*) AS ProductCount
3FROM     Products
4GROUP BY CategoryID;

1SELECT   CategoryID, 
2         COUNT(*) AS ProductCount
3FROM     Products
4GROUP BY CategoryID;

字段	类型
ProductID	int
SupplierID	int

ProductID	SupplierCount
1	2
2	2

1-- 写你的查询语句

1SELECT   ProductID, 
2         COUNT(*) AS SupplierCount
3FROM     Product_Suppliers
4GROUP BY ProductID;

1SELECT   ProductID, 
2         COUNT(*) AS SupplierCount
3FROM     Product_Suppliers
4GROUP BY ProductID;

字段	类型
CustomerID	int
CustomerName	varchar(50)
Email	varchar(50)
Phone	varchar(15)
Address	varchar(100)
LastLogin	date
CreatedAt	timestamp
CustomerTier	enum('New','Regular','VIP')
ReferralID	int
LoyaltyPoints	int
LastPurchaseDate	date
IsChurnRisk	tinyint(1)

ReferralID	Referrals
1	5
2	5

1-- 写你的查询语句

1SELECT   ReferralID, COUNT(*) AS Referrals
2FROM     Customers
3WHERE    ReferralID IS NOT NULL
4GROUP BY ReferralID;

1SELECT   ReferralID, COUNT(*) AS Referrals
2FROM     Customers
3WHERE    ReferralID IS NOT NULL
4GROUP BY ReferralID;

字段	类型
OrderID	int
CustomerID	int
OrderDate	date
TotalAmount	decimal(10,2)
ShippedDate	date
DeliveryStatus	enum('Pending','Shipped','Delivered','Cancelled')
EmployeeID	int
CreatedAt	timestamp
ExpectedDeliveryDate	date
ActualDeliveryDate	date
LateDelivery	tinyint(1)
PaymentMethod	enum('Credit Card','PayPal','Cash on Delivery')
ReturnCount	int
FraudRisk	tinyint(1)

字段	类型
CustomerID	int
CustomerName	varchar(50)
Email	varchar(50)
Phone	varchar(15)
Address	varchar(100)
LastLogin	date
CreatedAt	timestamp
CustomerTier	enum('New','Regular','VIP')
ReferralID	int
LoyaltyPoints	int
LastPurchaseDate	date
IsChurnRisk	tinyint(1)

CustomerID	NumOrders
2	3
7	2

1-- 写你的查询语句

1SELECT   c.CustomerID, 
2         COUNT(o.OrderID) AS NumOrders
3FROM     Customers c
4JOIN     Orders o ON c.CustomerID = o.CustomerID
5WHERE    c.CustomerTier = 'VIP'
6GROUP BY c.CustomerID;

1SELECT   c.CustomerID, 
2         COUNT(o.OrderID) AS NumOrders
3FROM     Customers c
4JOIN     Orders o ON c.CustomerID = o.CustomerID
5WHERE    c.CustomerTier = 'VIP'
6GROUP BY c.CustomerID;

字段	类型
ProductID	int
ProductName	varchar(50)
CategoryID	int
Price	decimal(10,2)
Stock	int
LastRestockDate	date
MonthlySales	int
InventoryTurnoverRate	decimal(5,2)

HighSalesProducts
6

1-- 写你的查询语句

1SELECT COUNT(*) AS HighSalesProducts
2FROM   Products
3WHERE  MonthlySales > 10;

1SELECT COUNT(*) AS HighSalesProducts
2FROM   Products
3WHERE  MonthlySales > 10;

字段	类型
OrderID	int
CustomerID	int
OrderDate	date
TotalAmount	decimal(10,2)
ShippedDate	date
DeliveryStatus	enum('Pending','Shipped','Delivered','Cancelled')
EmployeeID	int
CreatedAt	timestamp
ExpectedDeliveryDate	date
ActualDeliveryDate	date
LateDelivery	tinyint(1)
PaymentMethod	enum('Credit Card','PayPal','Cash on Delivery')
ReturnCount	int
FraudRisk	tinyint(1)

PaymentMethod	NumOrders
Credit Card	32
PayPal	16

1-- 写你的查询语句

1SELECT   PaymentMethod, 
2         COUNT(*) AS NumOrders
3FROM     Orders
4GROUP BY PaymentMethod;

1SELECT   PaymentMethod, 
2         COUNT(*) AS NumOrders
3FROM     Orders
4GROUP BY PaymentMethod;

字段	类型
OrderDetailID	int
OrderID	int
ProductID	int
Quantity	int
TotalItemPrice	decimal(10,2)

ProductID	TimesOrdered
1	3
2	3

1-- 写你的查询语句

1SELECT   ProductID, 
2         COUNT(*) AS TimesOrdered
3FROM     Order_Details
4GROUP BY ProductID
5HAVING   COUNT(*) > 1;

1SELECT   ProductID, 
2         COUNT(*) AS TimesOrdered
3FROM     Order_Details
4GROUP BY ProductID
5HAVING   COUNT(*) > 1;

字段	类型
OrderID	int
CustomerID	int
OrderDate	date
TotalAmount	decimal(10,2)
ShippedDate	date
DeliveryStatus	enum('Pending','Shipped','Delivered','Cancelled')
EmployeeID	int
CreatedAt	timestamp
ExpectedDeliveryDate	date
ActualDeliveryDate	date
LateDelivery	tinyint(1)
PaymentMethod	enum('Credit Card','PayPal','Cash on Delivery')
ReturnCount	int
FraudRisk	tinyint(1)

ReturnedOrders
2

1-- 写你的查询语句

1SELECT COUNT(*) AS ReturnedOrders
2FROM   Orders
3WHERE  ReturnCount > 0;

1SELECT COUNT(*) AS ReturnedOrders
2FROM   Orders
3WHERE  ReturnCount > 0;

字段	类型
OrderDetailID	int
OrderID	int
ProductID	int
Quantity	int
TotalItemPrice	decimal(10,2)

字段	类型
ProductID	int
ProductName	varchar(50)
CategoryID	int
Price	decimal(10,2)
Stock	int
LastRestockDate	date
MonthlySales	int
InventoryTurnoverRate	decimal(5,2)

ProductName	TotalQuantity
Novel B	8
Novel A	8

1-- 写你的查询语句

1SELECT   p.ProductName,
2         SUM(od.Quantity) AS TotalQuantity
3FROM     Order_Details od
4JOIN     Products p ON od.ProductID = p.ProductID
5GROUP BY p.ProductName
6ORDER BY TotalQuantity DESC
7LIMIT    5;

1SELECT   p.ProductName,
2         SUM(od.Quantity) AS TotalQuantity
3FROM     Order_Details od
4JOIN     Products p ON od.ProductID = p.ProductID
5GROUP BY p.ProductName
6ORDER BY TotalQuantity DESC
7LIMIT    5;

字段	类型
OrderID	int
CustomerID	int
OrderDate	date
TotalAmount	decimal(10,2)
ShippedDate	date
DeliveryStatus	enum('Pending','Shipped','Delivered','Cancelled')
EmployeeID	int
CreatedAt	timestamp
ExpectedDeliveryDate	date
ActualDeliveryDate	date
LateDelivery	tinyint(1)
PaymentMethod	enum('Credit Card','PayPal','Cash on Delivery')
ReturnCount	int
FraudRisk	tinyint(1)

字段	类型
OrderDetailID	int
OrderID	int
ProductID	int
Quantity	int
TotalItemPrice	decimal(10,2)

CustomerID	UniqueProducts
1	4
2	2

1-- 写你的查询语句

1SELECT   o.CustomerID,
2         COUNT(DISTINCT od.ProductID) AS UniqueProducts
3FROM     Orders o
4JOIN     Order_Details od ON o.OrderID = od.OrderID
5GROUP BY o.CustomerID;

1SELECT   o.CustomerID,
2         COUNT(DISTINCT od.ProductID) AS UniqueProducts
3FROM     Orders o
4JOIN     Order_Details od ON o.OrderID = od.OrderID
5GROUP BY o.CustomerID;

字段	类型
OrderID	int
CustomerID	int
OrderDate	date
TotalAmount	decimal(10,2)
ShippedDate	date
DeliveryStatus	enum('Pending','Shipped','Delivered','Cancelled')
EmployeeID	int
CreatedAt	timestamp
ExpectedDeliveryDate	date
ActualDeliveryDate	date
LateDelivery	tinyint(1)
PaymentMethod	enum('Credit Card','PayPal','Cash on Delivery')
ReturnCount	int
FraudRisk	tinyint(1)

CustomerID	TotalOrders
1	6
2	3

1-- 写你的查询语句

1SELECT   CustomerID, 
2         COUNT(*) AS TotalOrders
3FROM     Orders
4GROUP BY CustomerID
5HAVING   COUNT(*) > 2;

1SELECT   CustomerID, 
2         COUNT(*) AS TotalOrders
3FROM     Orders
4GROUP BY CustomerID
5HAVING   COUNT(*) > 2;

字段	类型
OrderID	int
CustomerID	int
OrderDate	date
TotalAmount	decimal(10,2)
ShippedDate	date
DeliveryStatus	enum('Pending','Shipped','Delivered','Cancelled')
EmployeeID	int
CreatedAt	timestamp
ExpectedDeliveryDate	date
ActualDeliveryDate	date
LateDelivery	tinyint(1)
PaymentMethod	enum('Credit Card','PayPal','Cash on Delivery')
ReturnCount	int
FraudRisk	tinyint(1)

EmployeeID	LateDeliveries
3	3
4	2

1-- 写你的查询语句

1SELECT   EmployeeID,
2         COUNT(*) AS LateDeliveries
3FROM     Orders
4WHERE    LateDelivery = TRUE AND EmployeeID IS NOT NULL
5GROUP BY EmployeeID;

1SELECT   EmployeeID,
2         COUNT(*) AS LateDeliveries
3FROM     Orders
4WHERE    LateDelivery = TRUE AND EmployeeID IS NOT NULL
5GROUP BY EmployeeID;

字段	类型
CustomerID	int
CustomerName	varchar(50)
Email	varchar(50)
Phone	varchar(15)
Address	varchar(100)
LastLogin	date
CreatedAt	timestamp
CustomerTier	enum('New','Regular','VIP')
ReferralID	int
LoyaltyPoints	int
LastPurchaseDate	date
IsChurnRisk	tinyint(1)

InactiveCustomers
11

1-- 写你的查询语句

1SELECT COUNT(*) AS InactiveCustomers
2FROM   Customers
3WHERE  LastPurchaseDate IS NULL;

1SELECT COUNT(*) AS InactiveCustomers
2FROM   Customers
3WHERE  LastPurchaseDate IS NULL;

开始入门

分组桶

开始入门

聚合模式

过滤模式

比较模式

序列与层级模式

转换模式

总结

计数统计

计数统计

计数统计模式的语法