tencent cloud

Cloud Log Service

ドキュメントCloud Log Service操作ガイドログトピックの検索分析統計分析（SQL）SQL事例

SQL事例

ダウンロード
フォーカスモード
フォントサイズ
最終更新日: 2026-10-12 14:32:17
AI翻訳

操作シナリオ

SQLを使用したログの統計分析は非常に柔軟で、ほとんどの統計分析ニーズを満たすことができます。しかし、SQL構文は比較的複雑で、習得が容易ではありません。本記事では、参考としてよく使われるSQL事例を紹介します。

サンプルデータ

本記事では、Nginx Demoログを例に、よく使われるSQLを紹介します。Demoログを使用してCLSをすばやく体験するを参照してNginx Demoログトピックを有効にし、本記事のSQLを実際に実行できます。Nginx Demoログの内容は以下の通りです：
{
	"remote_addr": "124.78.118.162",
	"method": "GET",
	"upstream_addr": "169.254.128.14:60002",
	"upstream_response_length": "48",
	"body_bytes_sent": "59",
	"time_local": "2021-12-18T05:16:06+00:00",
	"version": "HTTP/1.1",
	"url": "/_ignition/execute-solution",
	"http_user_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36",
	"remote_user": "-",
	"req_id": "37585e290318470aa57b148282aed99f",
	"upstream_status": "400",
	"request_time": "0.002",
	"sys_address": "11.149.171.195",
	"request_length": "272",
	"http_referer": "-",
	"sys_datasource": "gz.1.1.v1.2.19",
	"proxy_upstream_name": "default-kubernetes-443",
	"upstream_response_time": "0.000",
	"time": "1706701448003",
	"timestamp": "2024-01-31T19:44:08+08:00",
	"status": "400"
}

運用保守監視の事例

リクエスト数上位URL

* | select count(*) as "リクエスト数",
    url
    group by url
    order by "リクエスト数" desc
    limit 50
ステートメントの説明：
count(*)：すべてのログ件数を統計します。つまりリクエスト数です。
group by url：urlごとにグループ化し、各urlに対応するリクエスト数を統計します。
order by "リクエスト数" desc：リクエスト数の降順でソートし、リクエスト数の多いurlを優先的に表示します。
limit 50：クエリ結果の上位50件のみを返します。つまり、リクエスト数が最も多い50個のurlです。

リクエスト数上位URL（URLからパラメータ部分を除く）

* | select count(*) as "リクエスト数",
    url_extract_path(url) as url_path
    group by url_path
    order by "リクエスト数" desc
    limit 50
ステートメントの説明：
url_extract_path(url)：urlからパラメータ部分を除去し、pathのみを保持します。これにより、urlパラメータの変化が統計に影響を与えることを防ぎます。
その他の文法の意味は上記の例と同じです。

エラーリクエストの割合

* | select round(count_if(status >= 400)*1.0 / count(*),2) as "エラーリクエスト割合"
ステートメントの説明：
count_if(status >= 400)：指定条件を満たすログ件数を統計します。ここでは、httpステータスコードstatus >= 400をエラーリクエストとして扱います。
*1.0：1.0を乗算するのは、count_ifの結果をdouble型に変換するためです。これにより、後続の除算時に精度を保持できます。そうしないと、整数同士の除算結果は整数のままとなり、精度が失われます。
count(*)：すべてのログ件数を統計します。つまり総リクエスト数です。
round：小数点以下の桁数を保持します。ここでは2桁を保持します。

エラーリクエスト数のURL別分布

status >= 400 | select count(*) as "エラーリクエスト数",url
    group by url
    order by "エラーリクエスト数" desc
    limit 50
ステートメントの説明：
status >= 400：パイプ|の前のステートメントは検索条件です。この条件は、httpステータスコードstatus >= 400のログのみを対象に統計分析を行うことを意味します。
count(*)：すべてのログ件数を統計します。ただし、パイプ|の前にすでに検索条件があるため、ここで実際に統計されるのはすべてのエラーリクエストログ件数です。
group by url：urlごとにグループ化し、各urlに対応するエラーリクエスト数を統計します。
order by "エラーリクエスト数" desc：エラーリクエスト数の降順でソートし、エラーリクエスト数の多いurlを優先的に表示します。
limit 50：クエリ結果の上位50件のみを返します。つまり、エラーリクエスト数が最も多い50個のurlです。

エラーリクエスト割合の推移

* | select round(count_if(status >= 400)*1.0 / count(*),2) as "エラーリクエスト割合",
    histogram(__TIMESTAMP__,INTERVAL ${__interval}) as time
    group by time limit 10000
ステートメントの説明：
count_if(status >= 400)：指定条件を満たすログ件数を統計します。ここでは、httpステータスコードstatus >= 400をエラーリクエストとして扱います。
*1.0：1.0を乗算するのは、count_ifの結果をdouble型に変換するためです。これにより、後続の除算時に精度を保持できます。そうしないと、整数同士の除算結果は整数のままとなり、精度が失われます。
count(*)：すべてのログ件数を統計します。つまり総リクエスト数です。
round：小数点以下の桁数を保持します。ここでは2桁を保持します。
histogram(__TIMESTAMP__,INTERVAL ${__interval})：時間に基づいて指標の変化傾向を確認する場合、傾向図内の各データポイント間の間隔を指定する必要があります。例えば、直近60分間の変化傾向をクエリする場合、データポイント間の間隔は30秒です。ここでは：
__TIMESTAMP__はログ時間、つまりリクエストの発生日時を表します。
${__interval}はデータポイント間の間隔を表し、変数です。クエリ時間範囲の長さに基づいて最適な間隔が動的に計算されます。手動で指定することもできます。例えば、1 MINUTEに変更すると、間隔が1分であることを表します。
group by time：histogramで時間間隔を指定した後、その間隔ごとにエラーリクエストの割合をそれぞれ統計します。
limit 10000：SQLでlimitが指定されていない場合、デフォルトでは100件のクエリ結果のみが返されます。完全なクエリ結果を取得するには、limitにより大きな値を指定してください。

ステータスコード別リクエスト数の推移

* | select count(*) as cnt,status,
    histogram(__TIMESTAMP__,interval ${__interval}) as time 
    group by time,status limit 10000
ステートメントの説明：
count(*)：すべてのログ件数を統計します。つまり総リクエスト数です。
histogram(__TIMESTAMP__,INTERVAL ${__interval})：時間に基づいて指標の変化傾向を確認する場合、傾向図内の各データポイント間の間隔を指定する必要があります。例えば、直近60分間の変化傾向をクエリする場合、データポイント間の間隔は30秒です。ここでは：
__TIMESTAMP__はログ時間、つまりリクエストの発生日時を表します。
${__interval}はデータポイント間の間隔を表し、変数です。クエリ時間範囲の長さに基づいて最適な間隔が動的に計算されます。手動で指定することもできます。例えば、1 MINUTEに変更すると、間隔が1分であることを表します。
group by time：histogramで時間間隔を指定した後、その間隔ごとにエラーリクエストの割合をそれぞれ統計します。
limit 10000：SQLでlimitが指定されていない場合、デフォルトでは100件のクエリ結果のみが返されます。完全なクエリ結果を取得するには、limitにより大きな値を指定してください。

ステータスコード別リクエスト割合の推移

* | select time,status,
    cnt*1.0/sum(cnt) over (partition by time) as pct 
    from (
      select count(*) as cnt,status,
      histogram(__TIMESTAMP__,interval ${__interval}) as time 
      group by time,status)
    limit 10000
ステートメントの説明：
select count(*) as cnt......group by time,status：このクエリは各ステータスコード別リクエスト数変化傾向のケースと一致し、各ステータスコード別のリクエスト数変化傾向を統計します。
sum(cnt) over (partition by time)：サブクエリの結果に基づき、各時間間隔における異なるステータスコードのリクエスト数を合計し、その時間間隔のリクエスト総数とします。
cnt*1.0/sum(cnt) over (partition by time) as pct：各ステータスコードのリクエスト数をリクエスト総数で割り、そのステータスコードのリクエスト割合を取得します。ここで*1.0はcntをdouble型に変換するために使用されます。これは後続の除算で精度を保持するためです。そうしない場合、整数同士の除算結果は整数のままとなり、精度が失われます。

P95レイテンシが最も高いURL

* | select approx_percentile(request_time,0.95) as P95,
    url
    group by url
    order by P95 desc
    limit 50
ステートメントの説明：
approx_percentile(request_time,0.95)：request_timeを統計し、95%分位に位置する値を取得します。つまり、95%のリクエスト処理時間がこの値より短いことを表します。
group by url：urlごとにグループ化し、各urlに対応するP95を統計します。
order by P95 desc：P95の降順でソートし、P95が高いurlを優先的に表示します。
limit 50：クエリ結果の上位50件のみを返します。つまり、P95が最も高い50個のurlです。

P95レイテンシの推移

* | select approx_percentile(request_time,0.95) as P95,
    histogram(__TIMESTAMP__,INTERVAL ${__interval}) as time
    group by time limit 10000
ステートメントの説明：
approx_percentile(request_time,0.95)：request_timeを統計し、95%分位に位置する値を取得します。つまり、95%のリクエスト処理時間がこの値より短いことを表します。
histogram(__TIMESTAMP__,INTERVAL ${__interval})：時間に基づいて指標の変化傾向を確認する場合、傾向図内の各データポイント間の間隔を指定する必要があります。例えば、直近60分間の変化傾向をクエリする場合、データポイント間の間隔は30秒です。ここでは：
__TIMESTAMP__はログ時間、つまりリクエストの発生日時を表します。
${__interval}はデータポイント間の間隔を表し、変数です。クエリ時間範囲の長さに基づいて最適な間隔が動的に計算されます。手動で指定することもできます。例えば、1 MINUTEに変更すると、間隔が1分であることを表します。
group by time：histogramで時間間隔を指定した後、その間隔ごとに各間隔のP95処理時間をそれぞれ統計します。
limit 10000：SQLでlimitが指定されていない場合、デフォルトでは100件のクエリ結果のみが返されます。完全なクエリ結果を取得するには、limitにより大きな値を指定してください。

URL別P95レイテンシの推移（レイテンシが最も高い10件のURLのみ表示）

* | select approx_percentile(request_time,0.95) as P95,
    histogram(__TIMESTAMP__,INTERVAL ${__interval}) as time,
    url
    where url in  (select url group by url order by approx_percentile(request_time,0.95) desc limit 10)
    group by time,url
    order by P95 desc limit 10000
ステートメントの説明：
where url in (select url group by url order by approx_percentile(request_time,0.95) desc limit 10)：URLが多い場合、すべてのURLのそれぞれの処理時間の変化傾向を直接確認すると、傾向図が非常に見づらくなります。このフィルタ条件を使用することで、処理時間が最も高い10個のURLのみを確認できます。
その他の文法の意味は上記の例と同じです。

ネットワーク総トラフィックが最も高いurl

* | select sum(body_bytes_sent) as "ネットワークトラフィック",
    url
    group by url
    order by "ネットワークトラフィック" desc
    limit 50
ステートメントの説明：
sum(body_bytes_sent)：body_bytes_sentを合計し、ネットワーク総トラフィックを取得します。
group by url：urlごとにグループ化し、各urlに対応するネットワーク総トラフィックを統計します。
order by "ネットワークトラフィック" desc：P95の降順でソートし、ネットワーク総トラフィックが高いurlを優先的に表示します。
limit 50：クエリ結果の上位50件のみを返します。つまり、ネットワーク総トラフィックが最も高い50個のurlです。

バージョン番号範囲によるログのフィルタリング

* | select * 
where array_join(transform(split(version, '.'), x -> lpad(x, 10, '0')), '.') > array_join(transform(split('2.0.0', '.'), x -> lpad(x, 10, '0')), '.')

ステートメントの説明：
バージョン番号versionは文字列であり、例えば「2.0.1」の場合、検索条件で範囲指定によるフィルタリングを直接行うことはできません。そのため、SQLでフィルタリングする必要があります。上記のSQLは、バージョン番号が「2.0.0」以上のログをフィルタリングできます。
split(version, '.')：記号.を使用してバージョン番号を複数の部分に分割し、結果は配列になります。例えば、2.0.1を["2","0","1"]に変換します。
transform(split(version, '.'), x -> lpad(x, 10, '0'))：transform関数を使用して、配列内の各数値の左側に0を補い、長さをすべて10にします。これにより、バージョン番号の各部分の長さが不一致で後続の比較に影響することを防ぎます。例えば、2.0.1を["0000000002","0000000000","0000000001"]に変換します。
array_join：記号.を使用して配列を文字列に連結します。transform関数を使用してバージョン番号の各部分の長さをすべて10に統一済みのため、この時点の文字列は直接文字単位で比較できます。
注意：
バージョン番号の形式は統一する必要があります。例えば、2.0のものもあれば、2.0.0。のものもあります。
バージョン番号に数字以外の文字を含めることはできません。例えば、2.0.0-alphaです。
バージョン番号の各部分の長さは10を超えることはできません。

運用分析事例

URL別PVおよびUV

* | select count(*) as PV, approx_distinct(remote_addr) as UV, url
    group by url
    order by PV desc
    limit 50
ステートメントの説明：
approx_distinct(remote_addr)：クライアントIP remote_addrを重複排除して統計した数をUVとします。
group by url：urlごとにグループ化し、各urlに対応するPVおよびUVを統計します。
order by PV desc：PVの降順でソートし、PVの高いurlを優先的に表示します。
limit 50：クエリ結果の上位50件のみを返します。つまり、PVが最も高い50個のurlです。

UV国内省別分布

* | select approx_distinct(remote_addr) as UV, ip_to_province(remote_addr) as province
    where ip_to_province(remote_addr) != 'NULL' and ip_to_country(remote_addr) = '中国'
    group by province
    order by UV desc
    limit 1000
ステートメントの説明：
approx_distinct(remote_addr)：クライアントIP remote_addrを重複排除して統計した数をUVとします。
ip_to_province(remote_addr)：クライアントIP remote_addrに基づいてクライアントが所在する省份を取得します。
where ip_to_province(remote_addr) != 'NULL' and ip_to_country(remote_addr) = '中国'：省份情報のないIP（例えば内部ネットワークIP）を除外し、国内IPのみを保持します。
group by province：provinceごとにグループ化し、各provinceに対応するUVを統計します。
order by UV desc：UVの降順でソートし、PVの高いprovinceを優先的に表示します。
limit 50：クエリ結果の上位50件のみを返します。つまり、UVが最も高い50個のprovinceです。

UV前月比

* | 
select compare[1] as today, compare[2] as yesterday, round(compare[3],4) as ratio
from (
    select compare(UV, 86400) as compare
    from (
        select approx_distinct(remote_addr) as UV
    )
)
ステートメントの説明：
approx_distinct(remote_addr)：クライアントIP remote_addrを重複排除して統計した数をUVとします。
compare(UV, 100) as compare：サブクエリ内のUVについてその前日比を計算します。86400は86400秒前（つまり1日前）との比率を計算することを表します。結果は配列であり、順に現在のUV（compare[1]）、1日前のUV（compare[2]）、現在のUVと1日前のUVの比率（compare[3]）です。

PVモバイル端末タイプ別分布

* | select 
    case when 
           lower(http_user_agent) like '%iphone%' OR 
           lower(http_user_agent) like '%ipod%' OR 
           lower(http_user_agent) like '%ipad%' OR 
           lower(http_user_agent) like '%ios%'
         then 'IOS'
         when
           lower(http_user_agent) like '%android%'
         then 'Android'
         else 'other'
    end as type , 
    count(*) as PV
    group by type
    HAVING type != 'other'
ステートメントの説明：
case when：http_user_agentに含まれるキーワードに基づいて、それぞれ異なるデバイスタイプ（IOS、Android、other）に分類します。
lower(http_user_agent)：http_user_agentを小文字に変換し、キーワードのマッチングを容易にします。
count(*) as PV：すべてのログ件数を統計します。つまりPVです。
group by type：デバイスタイプごとにグループ化し、各デバイスタイプに対応するPVを統計します。
HAVING type != 'other'：統計結果に対して二次フィルタリングを行い、otherタイプのデバイスを除外します。

アクセス数上位IP

* | select count(*) as PV, remote_addr as IP
    group by IP
    order by PV desc
    limit 50
ステートメントの説明：
count(*) as PV：すべてのログ件数を統計します。つまりPVです。
group by IP：IPごとにグループ化し、各IPに対応するPVを統計します。
order by PV desc：PVの降順でソートし、PVの高いIPを優先的に表示します。
limit 50：クエリ結果の上位50件のみを返します。つまり、PVが最も高い50個のIPです。

前の記事へ: ウィンドウ関数次の記事へ: サンプリング分析

ヘルプとサポート

この記事はお役に立ちましたか？

フィードバック

キーワードで検索
    Enter keywords to search documentation