目次
(1) ウィンドウ関数の定義
ホームページ データベース mysql チュートリアル MySQL でウィンドウ関数を使用する方法

MySQL でウィンドウ関数を使用する方法

May 30, 2023 pm 03:10 PM
mysql

(1) ウィンドウ関数の定義

ウィンドウ関数は、OLAP 機能(Online Analytical Processing、オンライン分析処理)とも呼ばれ、主にデータをリアルタイムに分析・処理するために使用されます。 MySQL バージョン 8.0 より前では、ウィンドウ関数はサポートされていませんでしたが、このバージョン以降、ウィンドウ関数のサポートが提供されています。

# 开窗函数语法 
func_name(<parameter>) 
OVER([PARTITION BY <part_by_condition>] 
[ORDER BY <order_by_list> ASC|DESC])

ウィンドウ関数ステートメントの分析:
関数は 2 つの部分に分かれており、1 つの部分は関数名です。ウィンドウ関数の数は比較的少なく、合計で 11 個のウィンドウ関数集約関数しかありません (すべての集計関数はウィンドウ関数として使用できます)。関数の性質に応じて、パラメーターを記述する必要があるものと、そうでないものがあります。

もう 1 つの部分は over ステートメントです。over() を記述する必要があります。内部のパラメータはすべてオプションであり、必要に応じて選択して使用できます:

  • 第 1 章パラメータはフィールドによるパーティションであり、このフィールドに基づいてデータセットを複数の部分に分割することを意味します

  • 2 番目のパラメータはフィールドによる順序であり、各ウィンドウのデータはこれに基づいていますフィールド 昇順または降順に並べる

MySQL でウィンドウ関数を使用する方法

ウィンドウ関数はグループ化集計関数と似ており、どちらもフィールドを指定してデータを複数の部分に分割します。

  • ##SQL 標準では、OVER キーワードを使用してウィンドウ関数と集計関数を区別することにより、すべての集計関数をウィンドウ関数として使用することができます。

  • 集計関数はグループごとに 1 つの値のみを返しますが、ウィンドウ関数はグループごとに複数の値を返すことができます。

これら 11 個のウィンドウ関数の中で、実際の作業で最もよく使用されるのは、ROW_NUMBER()、RANK()、DENSE_RANK() の 3 つの並べ替え関数です。簡単なデータセットを通じてこれら 3 つのウィンドウ関数を学習しましょう。

# 首先创建虚拟的业务员销售数据 
CREATE TABLE Sales
( 
idate date, 
iname char(2), 
sales int
); 
# 向表中插入数据 
INSERT INTO Sales VALUES 
(&#39;2021/1/1&#39;, &#39;丁一&#39;, 200), 
(&#39;2021/2/1&#39;, &#39;丁一&#39;, 180), 
(&#39;2021/2/1&#39;, &#39;李四&#39;, 100), 
(&#39;2021/3/1&#39;, &#39;李四&#39;, 150), 
(&#39;2021/2/1&#39;, &#39;刘猛&#39;, 180), 
(&#39;2021/3/1&#39;, &#39;刘猛&#39;, 150), 
(&#39;2021/1/1&#39;, &#39;王二&#39;, 200), 
(&#39;2021/2/1&#39;, &#39;王二&#39;, 180), 
(&#39;2021/3/1&#39;, &#39;王二&#39;, 300), 
(&#39;2021/1/1&#39;, &#39;张三&#39;, 300), 
(&#39;2021/2/1&#39;, &#39;张三&#39;, 280), 
(&#39;2021/3/1&#39;, &#39;张三&#39;, 280); 
# 数据查询 
SELECT * FROM Sales; 
# 查询各月中销售业绩最差的业务员
SELECT month(idate),iname,sales, 
	ROW_NUMBER() 
	OVER(PARTITION BY month(idate) 
			 ORDER BY sales) as sales_order 
FROM Sales;

SELECT * FROM 
(SELECT month(idate),iname,sales, 
	 ROW_NUMBER() 
	 OVER(PARTITION BY month(idate) 
   ORDER BY sales) as sales_order FROM Sales) as t
WHERE sales_order=1;

MySQL でウィンドウ関数を使用する方法

# ROW_NUMBER()、RANK()、DENSE_RANK()的区别 
SELECT * FROM 
(SELECT month(idate) as imonth,iname,sales, 
ROW_NUMBER() 
OVER(PARTITION BY month(idate) ORDER BY sales) as row_order,
RANK() 
OVER(PARTITION BY month(idate) ORDER BY sales) as rank_order, 
DENSE_RANK() 
OVER(PARTITION BY month(idate) ORDER BY sales) as dense_order 
FROM Sales) as t;

MySQL でウィンドウ関数を使用する方法

ROW_NUMBER(): 順次並べ替え——1、2、3

RANK(): 並列並べ替え、繰り返しのシリアル番号をスキップします - 1、1、3
DENSE_RANK(): 並列で並べ替え、繰り返しのシリアル番号をスキップしません - 1、1、2

(2) ウィンドウ関数 実際のアプリケーション シナリオ

仕事や面接などで、ユーザーに連続ログイン日数やチェックイン日数を尋ねる必要がある場面に遭遇することがあります。以下に、ウィンドウ関数を使用してこのような問題を解決するアイデアを示します。

# 首先创建虚拟的用户登录表,并插入数据 
create table user_login
( 
user_id varchar(100), 
login_time datetime
); 

insert into user_login values 
(1,&#39;2020-11-25 13:21:12&#39;), 
(1,&#39;2020-11-24 13:15:22&#39;), 
(1,&#39;2020-11-24 10:30:15&#39;), 
(1,&#39;2020-11-24 09:18:27&#39;), 
(1,&#39;2020-11-23 07:43:54&#39;), 
(1,&#39;2020-11-10 09:48:36&#39;), 
(1,&#39;2020-11-09 03:30:22&#39;), 
(1,&#39;2020-11-01 15:28:29&#39;), 
(1,&#39;2020-10-31 09:37:45&#39;), 
(2,&#39;2020-11-25 13:54:40&#39;), 
(2,&#39;2020-11-24 13:22:32&#39;), 
(2,&#39;2020-11-23 10:55:52&#39;), 
(2,&#39;2020-11-22 06:30:09&#39;), 
(2,&#39;2020-11-21 08:33:15&#39;), 
(2,&#39;2020-11-20 05:38:18&#39;), 
(2,&#39;2020-11-19 09:21:42&#39;), 
(2,&#39;2020-11-02 00:19:38&#39;), 
(2,&#39;2020-11-01 09:03:11&#39;), 
(2,&#39;2020-10-31 07:44:55&#39;), 
(2,&#39;2020-10-30 08:56:33&#39;), 
(2,&#39;2020-10-29 09:30:28&#39;); 
# 查看数据 
SELECT * FROM user_login;

連続ログイン日数の計算には通常、次の 3 つの状況があります。

  • 各ユーザーの継続ログイン ステータスの表示

  • ユーザーごとの連続ログイン最大日数を表示

  • 一定期間内にN日以上ログインしたユーザーを表示

最初の状況: 各ユーザーの連続ログイン状況を確認する

実際の経験に基づいて、一定期間内にユーザーが複数の連続ログインを行う可能性があることがわかっています。この情報を出力する必要があるため、フィールド最終結果出力には、ユーザー ID、最初のログイン日、ログイン終了日、連続ログイン日数を含めることができます。

# 数据预处理:由于统计的窗口期是天数,所以可以对登录时间字段进行格式转换,将其变成日期格式然后再去重(去掉用户同一天内多次登录的情况) 
# 为方便后续代码查看,将处理结果放置新表中,一步一步操作 
create table user_login_date(
select distinct user_id, date(login_time) login_date from user_login);
# 处理后的数据如下: 
select * from user_login_date;

# 第一种情况:查看每位用户连续登陆的情况 
# 对用户登录数据进行排序 
create table user_login_date_1( 
select *,
rank() over(partition by user_id order by login_date) irank 
from user_login_date); 
#查看结果 
select * from user_login_date_1;
 
# 增加辅助列,帮助判断用户是否连续登录 
create table user_login_date_2( 
select *,
date_sub(login_date, interval irank DAY) idate  #data_sub从指定的日期减去指定的时间间隔
from user_login_date_1); 
# 查看结果 
select * from user_login_date_2; 

# 计算每位用户连续登录天数 
select user_id, 
min(login_date) as start_date, 
max(login_date) as end_date, 
count(login_date) as days 
from user_login_date_2 
group by user_id,idate;

# ===============【整合代码,解决用户连续登录问题】=================== 
select user_id, 
       min(login_date) start_date, 
       max(login_date) end_date, 
       count(login_date) days 
from (select *,date_sub(login_date, interval irank day) idate 
from (select *,rank() over(partition by user_id order by login_date) irank 
from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c 
group by user_id,idate;

2 番目のケース: 各ユーザーの連続ログイン最大日数を確認する

# 计算每个用户最大连续登录天数 
select user_id,max(days) from 
(select user_id, 
			 min(login_date) start_date, 
			 max(login_date) end_date, 
			 count(login_date) days 
from (select *,date_sub(login_date, interval irank day) idate 
from (select *,rank() over(partition by user_id order by login_date) irank 
from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c 
group by user_id,idate) as d 
group by user_id;

3 番目のケース: 特定の期間に N 日を超えてログインしているユーザーを確認する期間

10 月 29 日から 11 月 25 日までに 5 日以上連続してログインしたユーザーを表示する必要がある場合、どうすればこれを実現できますか? 。この要件は、最初のケースのクエリの結果を使用してフィルタリングすることもできます。

# 查看在这段时间内连续登录天数≥5天的用户 
select distinct user_id from 
(select user_id, 
		min(login_date) start_date, 
		max(login_date) end_date, 
		count(login_date) days 
from (select *,date_sub(login_date, interval irank day) idate 
from (select *,rank() over(partition by user_id order by login_date) irank 
from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c 
group by user_id,idate 
having days>=5
) as d;

この書き方でも結果は得られますが、この問題では少し面倒ですので、簡単な方法を紹介します: 新しい静的ウィンドウ関数 lead()を参照してください

select *, 
lead(login_date,4) over(partition by user_id order by login_date) as idate5 
from user_login_date;

リードこの関数には 3 つのパラメータがあります。最初のパラメータは指定された列 (ここではログイン日が使用されます)、2 番目のパラメータは現在の行から数行後の値です。ここでは、5 回目のログイン日である 4 が使用されています。 3 番目のパラメーターは、返された null 値を指定された値に置き換えることができる場合、ここでは 3 番目のパラメーターは使用されません。 over 句では、ウィンドウが user_id ごとにグループ化され、各ウィンドウ内のデータがログイン日の昇順に並べられます。

5 番目のログイン日 (login_date 1) を使用します。5 に等しい場合は、5 日間連続してログインしたことを意味します。null 値または 5 より大きい値を取得した場合は、ログインしたことを意味します。

# 计算第5次登录日期与当天的差值 
select *,datediff(idate5,login_date)+1 days 
from (select *,lead(login_date,4) over(partition by user_id order by login_date) idate5
from user_login_date) as a; 
# 找出相差天数为5的记录 
select distinct user_id 
from (select *,datediff(idate5,login_date)+1 as days 
from (select *,lead(login_date,4) over(partition by user_id order by login_date) idate5 
from user_logrin_date) as a)as b 
where days = 5;
[演習] Meituan テイクアウト プラットフォーム データ分析インタビューの質問 -- SQL

既存のトランザクション データ テーブル user_goods_table は次のとおりです:

MySQL でウィンドウ関数を使用する方法

今度、上司は、各ユーザーが購入したテイクアウト カテゴリの優先分布を知り、各ユーザーがどのテイクアウト カテゴリを最も多く購入しているかを調べたいと考えています。

rree

以上がMySQL でウィンドウ関数を使用する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

このウェブサイトの声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

ホットAIツール

Undress AI Tool

Undress AI Tool

脱衣画像を無料で

Undresser.AI Undress

Undresser.AI Undress

リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover

AI Clothes Remover

写真から衣服を削除するオンライン AI ツール。

Clothoff.io

Clothoff.io

AI衣類リムーバー

Video Face Swap

Video Face Swap

完全無料の AI 顔交換ツールを使用して、あらゆるビデオの顔を簡単に交換できます。

ホットツール

メモ帳++7.3.1

メモ帳++7.3.1

使いやすく無料のコードエディター

SublimeText3 中国語版

SublimeText3 中国語版

中国語版、とても使いやすい

ゼンドスタジオ 13.0.1

ゼンドスタジオ 13.0.1

強力な PHP 統合開発環境

ドリームウィーバー CS6

ドリームウィーバー CS6

ビジュアル Web 開発ツール

SublimeText3 Mac版

SublimeText3 Mac版

神レベルのコード編集ソフト(SublimeText3)

財務データストレージのためにMySQLを最適化します 財務データストレージのためにMySQLを最適化します Jul 27, 2025 am 02:06 AM

MySQLは金融システムに最適化する必要があります。1。財務データを使用して、10進数タイプを使用した精度を確保する必要があり、タイムゾーンの問題を回避するために時間分野でデータを使用する必要があります。 2。インデックス設計は合理的でなければなりません。フィールドの頻繁な更新を避けてインデックスを構築し、クエリの順序でインデックスを組み合わせ、定期的に役に立たないインデックスをクリーンにします。 3.トランザクションを使用して、一貫性を確保し、トランザクションの粒度を制御し、長いトランザクションを回避し、それに埋め込まれた非コア操作を回避し、ビジネスに基づいて適切な分離レベルを選択します。 4。時間ごとに履歴データを分割し、コールドデータをアーカイブし、圧縮テーブルを使用してクエリ効率を向上させ、ストレージを最適化します。

オブジェクトレベルの特権でMySQLを保護します オブジェクトレベルの特権でMySQLを保護します Jul 29, 2025 am 01:34 AM

Tosecuremysqleffectivilly、useobject-levelprivilegestolimituseracesseracessbadeds.beginbyunderstanding thatobject-leveligesapplytodatabases、Tables、orcolumns、subfice finercontrolthangloblabliledeges.next、適用

大規模なMySQLテーブルを管理するためのベストプラクティス 大規模なMySQLテーブルを管理するためのベストプラクティス Aug 05, 2025 am 03:55 AM

大規模なテーブルを扱う場合、MySQLのパフォーマンスと保守性が課題に直面し、構造設計、インデックス最適化、テーブルサブテーブル戦略などから開始する必要があります。オーバーレイインデックスを使用して、クエリ効率を向上させます。スロークエリログを定期的に分析し、無効なインデックスを削除します。 2。パーティションテーブルの合理的な使用:クエリとメンテナンスの効率を改善するための時間範囲やその他の戦略に従ってパーティションをかけますが、分割と削減の問題に注意を払う必要があります。 3.分離とライブラリの分離の読み取りと書き込みを検討してください。ライブラリの分離とテーブルの分離は、大量のデータを備えたシナリオに適しています。ミドルウェアを使用して、トランザクションとクロスストアのクエリの問題を評価することをお勧めします。早期計画と継続的な最適化が重要です。

MySQLデータベースレプリケーションフィルターの実装 MySQLデータベースレプリケーションフィルターの実装 Jul 28, 2025 am 02:36 AM

MySQLレプリケーションフィルタリングは、メインライブラリまたはスレーブライブラリで構成できます。メインライブラリは、ログボリュームの低減に適したBinlog-Do-DBまたはBinlog-Ignore-DBを介してBinlogの生成を制御します。データアプリケーションは、Replicate-Do-DB、Replicate-Ignore-DB、Replicate-Do-Table、Replicate-Ignore-Table、WildCard Rules Replicate-Wild-Do-Table、Replicate-Wild-Ignore-Tableによって制御されます。それはより柔軟で、データの回復を助長します。構成するときは、ルールの順序、クロスストアステートメントの動作に注意を払う必要があります。

コンテンツ管理システム(CMS)のMySQLの最適化 コンテンツ管理システム(CMS)のMySQLの最適化 Jul 28, 2025 am 03:19 AM

toimprovemysqlperformanceforcmsplatformslikewwordpress、最初にaCachinginginslikEridEdisoredisoredisoredisoredisoredisoredisoredisoredisoredisomcaching(承認可能)、およびandusepagecachingpluginstoservestaticfiles.sond、sedemysemysemysqlconfigurationiincurationbyincurationcurationcurationは

MySQLの切り捨て、削除、ドロップの違いは何ですか? MySQLの切り捨て、削除、ドロップの違いは何ですか? Aug 05, 2025 am 09:39 AM

deleteremovesspecificorallrows、keepstable-structure、asollollbackandtriggers、およびdoesnotresetauto-increment;

一般的なMySQL接続エラーのトラブルシューティング方法は? 一般的なMySQL接続エラーのトラブルシューティング方法は? Aug 08, 2025 am 06:44 AM

MySQLサービスが実行されているかどうかを確認して、sudosystemctlstatusmysqlを使用して確認および開始します。 2.リモート接続を許可してサービスを再起動するために、バインドアドレスが0.0.0.0に設定されていることを確認してください。 3. 3306ポートが開いているかどうかを確認し、ポートを許可するファイアウォールルールを確認して構成します。 4。「アクセス」エラーの場合、ユーザー名、パスワード、ホスト名を確認し、mysqlにログインしてmysql.userテーブルをクエリしてアクセス許可を確認する必要があります。必要に応じて、 'your_user'@'%'を使用するなど、ユーザーを作成または更新して承認します。 5. caching_sha2_passwordにより認証が失われた場合

Check Constraintsを使用してMySQLのデータルールを実施する方法は? Check Constraintsを使用してMySQLのデータルールを実施する方法は? Aug 06, 2025 pm 04:49 PM

MySQLは、バージョン8.0.16から効果的なドメインの完全性を強制するためのチェック制約をサポートしています。 1.テーブルを作成するときに制約を追加する:createTableを使用して、18歳以上、給与> 0、部門の制限値などのチェック条件を定義します。 2。テーブルを変更して制約を追加します。AlterTableadDconstraintを使用して、名前以外の名前などのフィールド値を制限します。 3.複雑な条件を使用する:終了日≥の日付や完了ステータスなどのマルチカラムロジックと式のサポートは、終了日を持つ必要があります。 4。制約の削除:AlterTabledRopConstraintを使用して、削除する名前を指定します。 5。注:mysql8.0.16、innodbまたはmyisamを引用する必要があります

See all articles