目錄
1.尋找連續值
方法一:使用自連接(Self-Join)
方法二:使用窗口函數(Window Functions)
2.尋找有重復的值
?GROUP BY子句
HAVING子句
常用聚合函數:
3.找不存在某屬性的值
not in
not exist
性能比較
使用場景
1.尋找連續值
方法一:使用自連接(Self-Join)
select distinct l1.num as ConsecutiveNums
from logs l1
join logs l2 on l1.id = l2.id - 1 and l1.num = l2.num
join logs l3 on l2.id = l3.id - 1 and l2.num = l3.num
-
自連接(Self-Join)
-
自連接是指將同一張表連接到自身。通過為同一張表賦予不同的別名(如
l1
、l2
、l3
),可以將表中的行與其他行進行比較。 -
在這個例子中,
l1
、l2
和l3
分別代表logs
表中的不同行,通過id
的偏移量來確定它們之間的順序關系。
-
-
連接條件(Join Conditions)
-
l1.id = l2.id - 1
:表示l2
的id
比l1
的id
大1,即l2
是l1
的下一行。 -
l1.num = l2.num
:表示l1
和l2
的num
值相同。 -
l2.id = l3.id - 1
:表示l3
的id
比l2
的id
大1,即l3
是l2
的下一行。 -
l2.num = l3.num
:表示l2
和l3
的num
值相同。 -
通過這些條件,確保了
l1
、l2
和l3
是連續的三行,并且它們的num
值相同。
-
-
DISTINCT關鍵字
-
DISTINCT
用于去除結果中的重復行,確保輸出的ConsecutiveNums
是唯一的。
-
方法二:使用窗口函數(Window Functions)
select distinct num as ConsecutiveNums
from (select num,LAG(num,1) over(order by id ASC) as pre1,LAG(num,2) over(order by id ASC) as pre2,LEAD(num,1) over(order by id ASC) as post1,LEAD(num,2) over(order by id ASC) as post2from logs
) AS subquery
where (pre2 = pre1 and pre1 = num)or (pre1 = num and num = post1)or (num = post1 and post1 = post2)
-
窗口函數(Window Functions)
-
窗口函數允許在結果集中對每一行進行計算,同時考慮其他行的值。
LAG
和LEAD
是兩種常用的窗口函數。 -
LAG(num,1) over(order by id ASC) as pre1
:獲取當前行的前一行的num
值。 -
LAG(num,2) over(order by id ASC) as pre2
:獲取當前行的前兩行的num
值。 -
LEAD(num,1) over(order by id ASC) as post1
:獲取當前行的下一行的num
值。 -
LEAD(num,2) over(order by id ASC) as post2
:獲取當前行的下兩行的num
值。
-
-
子查詢(Subquery)
-
子查詢用于生成一個臨時表(
subquery
),其中包含了原始表中的num
值以及通過窗口函數計算出的前后行的num
值。
-
-
WHERE子句
-
WHERE
子句用于篩選出滿足連續三次相同數字的行:-
(pre2 = pre1 and pre1 = num)
:當前行的num
值與其前兩行的num
值相同。 -
(pre1 = num and num = post1)
:當前行的num
值與其前一行和下一行的num
值相同。 -
(num = post1 and post1 = post2)
:當前行的num
值與其下一行和下兩行的num
值相同。
-
-
-
DISTINCT關鍵字
-
DISTINCT
用于去除結果中的重復行,確保輸出的ConsecutiveNums
是唯一的。
-
180. 連續出現的數字 - 力扣(LeetCode)
2.尋找有重復的值
SELECT DISTINCT email AS Email
FROM Person
GROUP BY email
HAVING COUNT(email) > 1;
?GROUP BY子句
-
GROUP BY email
:-
GROUP BY
用于將結果集按一個或多個列分組。這里按email
列分組,將具有相同email
值的行歸為一組。 -
分組后,每個
email
值只會出現一次,便于后續的聚合操作。
-
HAVING子句
-
HAVING COUNT(email) > 1
:-
HAVING
用于對分組后的結果進行篩選,類似于WHERE
子句,但HAVING
用于篩選分組后的聚合結果。 -
COUNT(email)
:計算每個分組中的行數,即每個email
值出現的次數。 -
HAVING COUNT(email) > 1
:篩選出出現次數大于1的email
值,即找出重復的email
-
常用聚合函數:
-
COUNT:計算某個列中非
NULL
值的數量,或使用COUNT(*)
計算表中的總行數。 -
SUM:計算數值列的總和,僅適用于數值類型的列。
-
AVG:計算數值列的平均值,僅適用于數值類型的列。
-
MAX:找出某個列中的最大值,適用于數值列或字符串列。
-
MIN:找出某個列中的最小值,適用于數值列或字符串列。
-
COUNT(DISTINCT):計算某個列中唯一值的數量,通過
DISTINCT
去除重復值后計數。 -
SUM(DISTINCT):計算某個列中唯一值的總和,通過
DISTINCT
去除重復值后求和。 -
AVG(DISTINCT):計算某個列中唯一值的平均值,通過
DISTINCT
去除重復值后求平均。 -
GROUP_CONCAT:將同一組中的值連接成一個字符串,可通過
SEPARATOR
指定分隔符。 -
STDDEV:計算數值列的標準差,用于衡量數據的離散程度。
-
VAR:計算數值列的方差,用于衡量數據的離散程度。
-
BIT_AND:計算一組值的按位與,用于位運算。
-
BIT_OR:計算一組值的按位或,用于位運算。
-
BIT_XOR:計算一組值的按位異或,用于位運算。
3.找不存在某屬性的值
not in
select name as Customers
from Customers
where Customers.id not in (select customerId from Orders
)
-
定義:
-
NOT IN
用于檢查某個值是否不在一個子查詢或值列表中。
-
-
語法:
SELECT column_name FROM table_name WHERE column_name NOT IN (subquery | value_list);
-
特點:
-
子查詢:
NOT IN
后面可以跟一個子查詢,返回一個值列表。 -
值列表:也可以直接跟一個具體的值列表。
-
性能:在某些情況下,
NOT IN
的性能可能不如NOT EXISTS
,尤其是在子查詢返回大量數據時。 -
空值處理:如果子查詢返回的列表中包含
NULL
,NOT IN
會返回空結果集,因為NULL
與任何值的比較結果都是UNKNOWN
。
-
not exist
SELECT name AS Customers
FROM Customers c
WHERE NOT EXISTS (SELECT 1FROM Orders oWHERE o.customerId = c.id
);
-
定義:
-
NOT EXISTS
用于檢查某個子查詢是否不返回任何行。
-
-
語法:
SELECT column_name FROM table_name WHERE NOT EXISTS (subquery);
-
特點:
-
子查詢:
NOT EXISTS
后面必須跟一個子查詢。 -
性能:通常比
NOT IN
更高效,尤其是在處理大量數據時。NOT EXISTS
會在找到第一個匹配的行時停止進一步檢查,而NOT IN
會檢查整個子查詢結果。 -
空值處理:
NOT EXISTS
不受NULL
值的影響,因為它只關心子查詢是否返回行,而不是具體的值。
-
性能比較
-
NOT IN
:-
適用于子查詢返回的值列表較小的情況。
-
如果子查詢返回大量數據,性能可能會下降。
-
對
NULL
值敏感,可能導致意外結果。
-
-
NOT EXISTS
:-
通常更高效,尤其是在處理大量數據時。
-
不受
NULL
值的影響。 -
邏輯上更清晰,尤其是在涉及多表連接時。
-
使用場景
-
NOT IN
:-
適用于簡單的值列表檢查。
-
適用于子查詢返回的值列表較小的情況。
-
-
NOT EXISTS
:-
適用于復雜的子查詢,尤其是涉及多表連接的情況。
-
適用于需要高效處理大量數據的情況。
-