14 高度なSQL拡張: カレンダ関数および集計フィルタ
「高度なSQL拡張: カレンダ関数および集計フィルタ」の章では、開発者が分析問合せを記述および管理する方法を簡素化する、Oracle SQLの強力な拡張機能を紹介します。これらの拡張機能は、冗長性の削減、可読性の向上、およびOracle SQLと業界標準との緊密な連携を目的として設計された、より広範なSelect for Analysis (SfA)イニシアチブの一部です。
従来の分析問合せでは、多くの場合、時間階層および条件付き集計を処理するために、CASE式、ネストされた副問合せまたはカスタム・ロジックの複雑な組合せが必要です。新しい拡張機能は、次の課題に直接対処します:
-
カレンダ関数は、グレゴリオ暦、会計カレンダ、小売(NRF 4-5-4)カレンダなど、複数の階層にわたって時間ベースの情報を抽出および書式設定するための組込みサポートを提供します。これにより、開発者は簡潔な構文で日付をグループ化、フィルタおよびナビゲートでき、出力を有効な日付に一意に変換して戻すことができます。
-
集計フィルタでは、ANSI SQL標準の
FILTER WHERE句をOracle SQLに導入します。これにより、開発者は問合せ内で集計ごとの条件を直接適用できるため、冗長な副問合せやエラーが発生しやすいCASE拡張が不要になります。結果として、より簡潔で直感的なコードが生成され、データ・リダクションなどの複雑なシナリオでも正しい結果が得られます。
これらの拡張機能を組み合せることで、開発者は次のことが可能になります:
-
時間ベースの分析のために、より短く表現しやすい問合せを記述します。
-
会計カレンダおよび小売カレンダをSQLの不可欠な構成要素として管理します。
-
明確さや正確さを損なうことなく、条件付きフィルタを集計に適用します。
-
SQL標準との互換性を維持し、移植性を確保し、導入を容易にします。
これらの機能をマスターすることで、開発者は分析レポートを合理化し、問合せの複雑さを軽減し、Oracle AI Database 26aiの柔軟性を新しいレベルに引き上げることができます。
14.1 カレンダ関数
カレンダ関数は、開発者が異なるカレンダ階層間で時間ベースの情報を日付列から直接抽出および操作できるようにすることで、分析問合せを簡略化するように設計されています。これらの関数は、標準カレンダ(グレゴリオ)、会計階層および小売階層をサポートし、様々なビジネス・コンテキストにわたる柔軟なレポートを可能にします。これらの関数は分析問合せにとって重要であり、一貫性と正確性を確保しながら、様々な時間ディメンションをグループ化およびナビゲーションできます。
-
分析問合せ: 開発者は、多くの場合、カレンダ期間でデータをグループ化またはフィルタする必要があります。これらの関数は、このような操作の簡潔な構文を提供します。
-
複数階層: 1つの日付列で異なる階層(カレンダ、会計、小売)を表すことができます。関数を使用すると、それらをシームレスに切り替えることができます。
-
一貫性: 同じ書式文字列を使用して出力を
DATEに変換して戻すことが保証され、信頼性の高い稠密化と分析が保証されます。
この関数は、次のいずれかを戻します:
-
期間を一意に識別する書式設定されたテキスト値(
VARCHAR2)または -
期間の開始または終了を表す日付値(
DATE)。
これらの関数は、EXTRACTのような既存のSQL行関数と同様に動作するようにモデル化されていますが、カレンダ階層のセマンティクスが強化されています。PL/SQL問合せ内も含めて、SQL行関数を使用できる場所であればどこでもアクセスできます。
14.1.1 カレンダ関数の使用
分析問合せでは、多くの場合、ファクト表の単一の日付列が時間ディメンションとして機能します。
この時間ディメンションは次のものを表すことができます:
-
標準のカレンダ階層(グレゴリオ暦)。
-
年がカスタム日付で開始する会計階層。
-
全米小売業協会(NRF)の4-5-4カレンダなどの小売階層。
これらのユースケースをサポートするために、Oracleでは一連のカレンダ関数が導入されています。これらの関数は、既存のEXTRACT行関数の機能を拡張し、開発者は複数のカレンダ・タイプにわたってレベル固有の情報(年、四半期、月、週、日)を取得できます。
関数は、カレンダ階層、会計階層および小売階層と、日、週、月、四半期、年レベルのすべての組合せに対応するように導入されています。これらの関数は、DATE式、または暗黙的にDATEに変換される任意の式を受け入れます。各関数の戻り値は、日付を表す書式設定された文字列であり、各関数に対してデフォルトの書式が明示的に定義されています。
一般的な構文
次に、カレンダ関数で使用される構文の一般化バージョンを示します:
CALENDAR (
<date_expression>
[ INDEX_BY <column_or_expression> ]
[ IS_RESTATED <boolean_flag> ]
[ START_DATE <date> ]
[ END_DATE <date> ]
[ INTERVAL <time_unit> ]
)
-
INDEX_BYは、カレンダ出力をグループ化または索引付けするために使用する列または式を定義します。例:INDEX_BY monthは、月ごとに結果を編成します。 -
IS_RESTATEDは、値が元のレポートと比較して修正(調整)されるかどうかを示すブール・フラグ(TRUE/FALSE)です。これは、RAWデータと調整済データを区別するために財務分析で役立ちます。 -
START_DATE/END_DATEは、カレンダ範囲の境界を指定します。 -
INTERVALは、カレンダの粒度(DAY、MONTH、QUARTER、YEAR)を設定します。
カレンダ関数のカテゴリ
カレンダ関数は次のカテゴリに分けられます:
-
書式設定されたテキスト関数は、年、四半期、月、週または日の期間(カレンダ、会計、小売)を表す
VARCHAR2文字列を戻します。例:CALENDAR_YEAR、FISCAL_MONTH、RETAIL_QUARTER。 -
開始日終了日関数は、期間境界(指定期間の開始日または終了日)の
DATE値(午前0時に設定された時間)を戻します。例:CALENDAR_QUARTER_START_DATE、FISCAL_YEAR_END_DATE、RETAIL_MONTH_START_DATE。 -
X of Y関数は、その祖先期間内の期間の位置を表す数値を戻します。例:
CALENDAR_DAY_OF_WEEK、FISCAL_MONTH_OF_YEAR、RETAIL_WEEK_OF_MONTH -
X期間の追加関数では、時間の構成要素を保持しながら期間演算を実行し、
DATE値を戻します。例:CALENDAR_ADD_MONTHS、FISCAL_ADD_MONTHS、RETAIL_ADD_DAYS -
Since関数およびユーティリティ関数は、指定された日付と
SYSDATEの間の判読可能な間隔を計算し、RETAIL_DAY_EXISTSなどのユーティリティ関数を使用します
これらの関数を使用するタイミング
-
分析問合せでカレンダ、会計期間または小売期間別にグループ化または集計する場合。
-
期間ラベルと
DATEの間の1対1マッピングが必要な稠密化および時系列操作の場合。 -
個別の時間ディメンション表を保持せずに、複数のカレンダ階層(カレンダ、会計、小売)への単一の日付列を変換する場合。
-
時刻構成要素を保持したまま、期間ナビゲーション(月の追加、週の追加など)を実行する場合。
以降の項では、各カレンダ関数カテゴリの詳細な説明を示し、詳細な例で補足します。
関連項目:
カレンダ関数の構文とセマンティックの詳細は、『Oracle AI Database SQL言語リファレンス』のカレンダ関数を参照してください
14.1.2 書式設定されたテキスト関数
書式設定されたテキスト関数を使用すると、開発者は、カレンダ階層、会計階層および小売階層内の様々な粒度の詳細(年、四半期、月、週、日)のグループを取得できます。出力は、TO_CHARに似た書式設定されたテキストですが、より厳密なルールを使用して、一意性と可逆性をDATEに戻します。書式文字列には、同じ書式文字列を使用して出力を関数の粒度でDATEに変換できるように、必要な指定子を含める必要があります。
これらの関数を使用すると、カレンダ階層別にグループ化できます。たとえば、カレンダ年、会計年度または小売年別の売上を計算します。次のSQL問合せは、カレンダ関数を使用して、ファクト表の同じ日付列から様々な時間階層(カレンダ年、会計年度および小売年)にわたる売上データを集計する方法を示しています。
SELECT calendar_year(f.day), region, SUM(f.sales)
FROM sales_fact f
JOIN TO ONE (customer_tbl)
GROUP BY ALL;
SELECT fiscal_year(f.day), region, SUM(f.sales)
FROM sales_fact f
JOIN TO ONE (customer_tbl)
GROUP BY ALL;
SELECT retail_year(f.day), region, SUM(f.sales)
FROM sales_fact f
JOIN TO ONE (customer_tbl)
GROUP BY ALL;
これらの例では、異なるカレンダ階層に従って、同じ日付列(f.day)を柔軟に解釈できることを示しています。アナリストは、calendar_year、fiscal_yearまたはretail_yearを使用することで、基礎となるデータを再構築することなく、ビジネス・ニーズにあわせて調整された一貫した集計を生成できます。
書式設定されたテキスト関数は、次の3つのカテゴリに分類されます:
-
カレンダ関数(グレゴリオ暦)
-
会計関数(会計年度開始によってずらして設定されたグレゴリオ暦)
-
小売関数(NRF 4-5-4小売カレンダ)
14.1.2.1 カレンダ関数
カレンダ関数は、標準のグレゴリオ暦に基づいて操作します。書式指定子は、TO_CHARの場合と同様に動作します。
表14-1カレンダ関数のサマリー
| 関数 | 説明: | 必要な書式 | デフォルト |
|---|---|---|---|
|
|
グレゴリオ暦の書式設定された年ラベル( |
4桁の年書式( |
デフォルトの書式は |
|
|
グレゴリオ暦の四半期ラベル( |
四半期( |
"Q"Q-SYYYY 例: Q1-2024 |
|
|
グレゴリオ暦の月ラベル( |
4桁の年(YYYYまたはSYYYY)と |
|
|
|
グレゴリオ暦の週ラベル( |
4桁の年(YYYYまたはSYYYY)と |
|
|
|
グレゴリオ暦の日ラベル( |
4桁の年、月( |
|
例14-1 カレンダ年
次の例は、日付からカレンダ年を抽出する方法を示しています:
SELECT CALENDAR_YEAR(DATE '2025-03-15', 'YYYY') YEAR
FROM DUAL;
次の出力が生成されます:
YEAR
----
2025
例14-2 2桁の年(終了日+TO_CHARを使用)
SELECT TO_CHAR(CALENDAR_YEAR_END_DATE(DATE '2025-03-15'), 'YY') YEAR
FROM DUAL;
次の出力が生成されます:
YEAR
----
25
14.1.2.2 会計関数
会計階層は、グレゴリオ暦から会計年度の開始日をずらして設定します。
-
開始日は、関数の引数(
fis_year_start)として、またはCALENDAR_FISCAL_YEAR_STARTパラメータを使用して指定できます。 -
会計年度は、最終日が含まれるカレンダ年によって決まります。
表14-2 会計関数のサマリー
| 関数 | 説明: | 必要な書式 | デフォルト |
|---|---|---|---|
|
|
会計年度ラベル( |
4桁の年(YYYYまたはSYYYY)が必要です/オプションの |
|
|
|
会計四半期ラベル( |
|
|
|
|
会計月ラベル( |
4桁の年(YYYYまたはSYYYY)が必要です/オプションの |
|
|
|
会計週ラベル( |
|
|
|
|
会計日ラベル( |
4桁の年、月( |
|
例14-3 会計年度開始 = 6月1日
次の例は、会計年度開始日が6月1日の会計年度を示しています:
SELECT FISCAL_YEAR(DATE '2025-05-15', DATE '2025-06-01') MAY_FY,
FISCAL_YEAR(DATE '2025-06-15', DATE '2025-06-01') JUN_FY
FROM DUAL;
次の出力が生成されます:
MAY_FY JUN_FY
------ ------
FY2025 FY2026
2025年5月15日を含む会計年度の最終日は2025年5月31日であるため、会計年度2025に該当します。2025年6月15日を含む会計年度の最終日は2026年5月31日であるため、会計年度2026に該当します。
例14-4 会計月開始 = 6月5日
会計年度が月の最初の日以外の日に開始する場合、前述のロジックも月に適用されます。次の例は、会計年度が6月5日に開始する月を示しています:
SELECT FISCAL_MONTH(DATE '2025-05-15', DATE '2025-06-05') MON1,
FISCAL_MONTH(DATE '2025-06-01', DATE '2025-06-05') MON2,
FISCAL_MONTH(DATE '2025-06-15', DATE '2025-06-05') MON3
FROM DUAL;
次の出力が生成されます:
MON1 MON2 MON3
---------- ---------- ----------
JUN-FY2025 JUN-FY2025 JUL-FY2026
14.1.2.3 小売関数
小売関数は、NRF 4-5-4カレンダを実装します。
-
小売年は日曜日に始まります(1月29日から2月4日)。
-
うるう週(53週目)は5–6年ごとに追加されます。
-
オプションの
is_restatedパラメータは、修正再表示された年(52週間)を処理します。
表14-3 小売関数のサマリー
| 関数 | 説明: | 必要な書式 | デフォルト |
|---|---|---|---|
|
|
NRF 4‑5‑4ルールを使用して小売年ラベルを戻します( |
4桁の年(YYYYまたはSYYYY)が必要です/オプションの |
|
|
|
小売四半期ラベル( |
|
|
|
|
小売月のラベル( |
4桁の年(YYYYまたはSYYYY)および |
|
RETAIL_WEEK |
小売週ラベル( |
|
"W"WW-"RY"SYYYY。例: W01-RY2024
|
RETAIL_DAY |
小売日ラベル( |
4桁の年、月( |
|
例14-5 小売年2023のうるう週
次の例は、必要に応じて53週目を追加する方法を示しています:
-- Retail year 2022 (normal 52 weeks)
Last day of 52nd week: January 28, 2023 → 3 days remain → no leap week
-- Retail year 2023 (53 weeks)
Last day of 52nd week: January 27, 2024 → 4 days remain → leap week added (Jan 28 – Feb 3, 2024)
例14-6 修正再表示された小売年エラー
修正再表示された小売年を使用すると、無効な日付でエラーが発生します:
SELECT RETAIL_MONTH ( order_date , ' DEFAULT ' , 'RESTATED' ) month ,
SUM ( sales ) sales
FROM fact
GROUP BY ALL
ORDER BY 1 ;
次の出力が生成されます:
-- ORA - xxx: The day does not exist in the restated retail calendar
例14-7 RETAIL_DAY_EXISTSを使用したコールの保護
次の例は、RETAIL_DAY_EXISTSを使用して無効な行をフィルタする方法を示しています:
SELECT RETAIL_MONTH ( order_date , ' DEFAULT ' , 'RESTATED' ) month ,
SUM ( sales ) sales
FROM fact
WHERE RETAIL_DAY_EXISTS ( order_date , 'RESTATED' ) = TRUE
GROUP BY ALL
ORDER BY 1 ;次の出力が生成されます:
MONTH SALES
---------- -----
FEB - RY2023 123
例14-8 無効な日数のCASE式を使用したコールの保護
開発者はCASE式も使用できます:
SELECT CASE
WHEN RETAIL_DAY_EXISTS ( order_date , 'RESTATED' ) = TRUE
THEN RETAIL_MONTH ( order_date , ' DEFAULT ' , 'RESTATED' )
ELSE ' #INVALID_DAY#'
END month ,
SUM ( sales ) sales
FROM fact
GROUP BY ALL
ORDER BY 1 ;
次の出力が生成されます:
MONTH SALES
------------- -----
FEB - RY2023 123
#INVALID_DAY# 456
例14-9 小売月の集計
次の例は、修正再表示されたカレンダとエラー処理を含む小売月の集計を示しています:
SELECT RETAIL_MONTH ( order_date , ' DEFAULT ' , 'RESTATED' ) month ,
SUM ( sales ) sales
FROM fact
GROUP BY ALL
ORDER BY 1 ;
14.1.3 開始日終了日関数
開始日終了日関数は、指定された期間(年、四半期、月、週)およびカレンダ・バリアント(カレンダ、会計、小売)の開始または終了のDATE (午前0時)を戻します。戻り値は、時間の構成要素が午前0時に設定されたDATEです。これらの関数は、範囲計算、稠密化、および時間ウィンドウの位置合せに役立ちます。
-
会計バリアントは、関数の引数
fis_year_startを受け入れるか、セッション/システム・パラメータCALENDAR_FISCAL_YEAR_STARTを使用します。 -
小売バリアントは
is_restatedを受け入れます。
関連項目:
開始日終了日関数の構文とセマンティックの詳細は、『Oracle AI Database SQL言語リファレンス』のカレンダ関数を参照してください
表14-4 開始日終了日関数のサマリー
| 関数 | 説明: |
|---|---|
|
|
入力日を含むカレンダ年のグレゴリオ暦の開始/終了の |
|
|
入力日を含むカレンダ四半期のグレゴリオ暦の開始/終了の |
|
|
入力日を含むカレンダ月のグレゴリオ暦の開始/終了の |
|
|
入力日を含むカレンダ週のグレゴリオ暦の開始/終了の |
|
|
入力日を含むカレンダ年の会計の開始/終了の |
|
|
会計四半期境界を戻します。オプションの |
|
|
会計月境界を戻します。オプションの |
|
|
会計週境界を戻します。オプションの |
|
|
NRF 4‑5‑4ルールごとに小売年境界を戻します。オプションの |
|
|
小売四半期境界を戻します。オプションの |
|
|
小売月境界(週)を戻します。オプションの |
|
|
小売週境界を戻します。オプションの |
例14-10 開始日関数と終了日関数
SELECT CALENDAR_QUARTER_START_DATE ( DATE ' 2025 - 06 - 15 ' ) cqs ,
FISCAL_YEAR_END_DATE ( DATE ' 2025 - 06 - 15 ' , DATE ' 2025 - 06 - 01 ' ) fye ,
RETAIL_MONTH_START_DATE ( DATE ' 2025 - 06 - 15 ' ) rms
FROM DUAL ;
次の出力が生成されます:
CQS FYE RMS
----------- ----------- -----------
01 - APR - 2025 31 - MAY - 2026 01 - JUN - 202514.1.4 X of Y関数
X of Y関数は、祖先期間内の日付の特定の構成要素を表す数値位置(NUMBER)を戻します。CALENDAR_MONTH_OF_YEAR、FISCAL_DAY_OF_MONTHおよびRETAIL_WEEK_OF_MONTHは、それぞれカレンダの年の月、会計の月の日および小売の月の週を表すいくつかの例です。年の構成要素には祖先がないため、これらの関数は単純にCALENDAR_YEAR_NUMBER、FISCAL_YEAR_NUMBERおよびRETAIL_YEAR_NUMBERです。
一部の関数では、index_byパラメータ('DATE'または'POSITION')を受け入れて、位置の番号付けと日付ベースの番号付けのいずれかを選択します:
-
index_byは、'DATE'(NLSテリトリに従う)または'POSITION'(週/月内の位置)です。 -
会計バリアントおよび小売バリアントは、それぞれのオプション・パラメータを受け入れます。
関連項目:
X of Y関数の構文とセマンティックの詳細は、『Oracle AI Database SQL言語リファレンス』のカレンダ関数を参照してください
表14-5 X of Y関数のサマリー
| 関数 | 説明: |
|---|---|
|
|
入力日の数値カレンダ年の番号 |
|
|
カレンダ年内の四半期番号(1から4) |
|
|
カレンダ年内の月番号(1から12) |
|
|
四半期内の月番号 |
|
|
カレンダ年内の週番号(1から53) |
|
|
カレンダ年内の日番号(1-366) |
|
|
四半期内の日番号 |
|
|
月内の日番号 |
|
|
曜日番号。 |
|
|
入力日の数値の会計年度番号(会計年度の最終日に基づく) |
|
|
会計年度内の四半期番号。オプションの |
|
|
会計年度内の月番号。オプションの |
|
|
会計四半期内の月番号。オプションの |
|
|
会計年度内の週番号。オプションの |
|
|
会計年度内の日番号。オプションの |
|
|
会計四半期内の日番号。オプションの |
|
|
会計月内の日番号。オプションの |
|
|
会計曜日。オプションの |
|
|
入力日の数値の小売年番号。オプションの |
|
|
小売年内の四半期番号。オプションの |
|
|
小売年内の小売月番号。オプションの |
|
|
小売四半期内の小売月番号。オプションの |
|
|
小売年内の小売週番号。オプションの |
|
|
小売四半期内の小売週番号。オプションの |
|
|
小売月内の小売週番号。オプションの |
|
|
小売年内の日番号。オプションの |
|
|
小売四半期内の日番号。オプションの |
|
|
小売月内の日番号。オプションの |
|
|
小売曜日。オプションの |
例14-11
次の例は、index_byパラメータを指定したCALENDAR_DAY_OF_WEEKを示しています:
SELECT CALENDAR_DAY_OF_WEEK ( DATE ' 2025 - 01 - 01 ' , ' DATE ' ) dt ,
CALENDAR_DAY_OF_WEEK ( DATE ' 2025 - 01 - 01 ' , 'POSITION' ) pos
FROM DUAL ;
カレンダ
X of Y関数のカレンダはすべて単純ですが、CALENDAR_DAY_OF_WEEKは例外で、オプションのindex_byパラメータが含まれています。このパラメータは、'POSITION'または'DATE' (デフォルト)のいずれかです。
-
'DATE'に設定すると、この数字は、
NLS_TERRITORY(Sunday=1、Monday=2など)に基づいて日付がどの曜日に当たるかを示します。 -
'POSITION'に設定すると、その年の週内の日の位置を示す索引を表します。
たとえば、2025年1月1日は2025年の第1週に始まり、水曜日になります:
SELECT CALENDAR_DAY_OF_WEEK ( DATE '2025-01-01', 'DATE' ) dt ,
CALENDAR_DAY_OF_WEEK ( DATE '2025-01-01', 'POSITION' ) pos
FROM DUAL ;
次の出力が生成されます:
DT POS
-- ---
4 1
会計
index_byパラメータも同様にFISCAL_DAY_OF_WEEKに含まれますが、FISCAL_MONTH_OF_YEARにも含まれます。
-
この場合、'DATE'が使用されると、その数は関連する会計月の月番号(Jan=1、Feb=2など)に対応します。
-
'POSITION'を使用すると、会計年度の初めからの月番号を表します。
たとえば:
SELECT FISCAL_MONTH_OF_YEAR ( DATE '2025-07-04', DATE '2025-06-01', 'DATE' ) dt ,
FISCAL_MONTH_OF_YEAR ( DATE '2025-07-04', DATE '2025-06-01', 'POSITION' ) pos
FROM DUAL ;
次の出力が生成されます:
DT POS
-- ---
7 2
小売
小売関数には、RETAIL_MONTH_OF_YEARおよびRETAIL_DAY_OF_WEEKのindex_byパラメータも含まれています。
小売には、カレンダまたは会計用に定義されていない2つの関数(RETAIL_WEEK_OF_QUARTERおよびRETAIL_WEEK_OF_MONTH)も含まれています。これらは、小売月が週単位(4または5)で定義され、さらに小売四半期が週単位で3か月として定義されるため、小売用に提供されます。
14.1.5 X期間の追加関数
X期間の追加関数は、特定の粒度(たとえば、CALENDAR_ADD_MONTHS、FISCAL_ADD_WEEKSおよびRETAIL_ADD_YEARS)間でDATE間を移動できるようにするために用意されています。これらの関数は、NUMBER (整数)を取得し、指定された期間数(年、月など)を指定のDATEに追加して、新しいDATEを生成します。指定されたDATEの時間(時間/分/秒)構成要素は、戻されたDATEに保持されます。間隔が負の場合、期間は減算されます。
X期間の追加関数を使用すると、期間(年、四半期、月、週、日)をまたいで前後にナビゲートできます。これらの関数は、入力DATEの時刻構成要素を保持します。
-
num_periodsは整数で、正の場合は加算、負の場合は減算されます。 -
会計バリアントと小売バリアントは、カレンダ・ルールに従います(たとえば、会計月は1日以外の日付で開始し、小売では修正再表示されたカレンダで日数がスキップされる場合があります)。
これらの関数は、カレンダ期間、会計期間、および小売期間に対して定義されます。
関連項目:
X期間の追加関数の構文とセマンティックの詳細は、『Oracle AI Database SQL言語リファレンス』のカレンダ関数を参照してください
表14-6 X期間の追加関数のサマリー
| 関数 | 説明: |
|---|---|
|
|
カレンダ期間の演算。 |
|
|
会計期間演算。オプションの |
|
|
小売期間の演算。オプションの |
カレンダ
CALENDAR_ADD_MONTHS関数は、既存のADD_MONTHS関数と同じであり、完全性のために用意されています。指定した日付が月の最終日の場合、または結果の月の日数が指定した日付の日の構成要素よりも少ない場合、結果の月の最終日が使用されます。それ以外の場合は、結果の月内の同じ日が使用されます。
例14-12 CALENDAR_ADD_MONTHS
次の例は、CALENDAR_ADD_MONTHSの動作を示しています:
SELECT CALENDAR_ADD_MONTHS ( DATE ' 2025 - 02 - 28 ' , 1 ) feb28 ,
CALENDAR_ADD_MONTHS ( DATE ' 2025 - 02 - 27 ' , 1 ) feb27 ,
CALENDAR_ADD_MONTHS ( DATE ' 2025 - 01 - 30 ' , 1 ) jan30 ,
CALENDAR_ADD_MONTHS ( DATE ' 2025 - 01 - 31 ' , 1 ) jan31
FROM DUAL ;
次の出力が生成されます:
FEB28 FEB27 JAN30 JAN31
----------- ----------- ----------- -----------
31-MAR-2025 27-MAR-2025 28-FEB-2025 28-FEB-2025
会計
ほとんどの会計関数は、カレンダの対応する関数と実質的に同等ですが、常にそうではありません。会計年度の開始が月の最初の日でない場合、月の最終日を決定する方法が完全に変わります。
例14-13
次の例は、会計年度が6月5日に開始した場合の会計のADD_MONTHSの違いを示しています:
SELECT CALENDAR_ADD_MONTHS ( DATE ' 2025 - 01 - 31 ' , 1 ) cal ,
FISCAL_ADD_MONTHS ( DATE ' 2025 - 01 - 31 ' , 1 , DATE ' 2025 - 06 - 05 ' ) fis
FROM DUAL ;
次の出力が生成されます:
CAL FIS
----------- -----------
28-FEB-2025 03-MAR-2025カレンダの場合、1月31日は月の最終日であるため、次の月の最終日(2月28日)が戻されます。6月5日に開始する会計カレンダでは、1月31日は1月5日から2月4日までの会計月に入っています。1月31日はその月の27日目であるため、翌月(2月5日から3月4日)の27日目(3月3日)が戻されます。
例14-14
次の例は、数日間のFISCAL_ADD_MONTHSのマッピングを示しています:
SELECT FISCAL_ADD_MONTHS ( DATE ' 2025 - 02 - 01 ' , 1 , DATE ' 2025 - 06 - 05 ' ) feb1 ,
FISCAL_ADD_MONTHS ( DATE ' 2025 - 02 - 02 ' , 1 , DATE ' 2025 - 06 - 05 ' ) feb2 ,
FISCAL_ADD_MONTHS ( DATE ' 2025 - 02 - 03 ' , 1 , DATE ' 2025 - 06 - 05 ' ) feb3 ,
FISCAL_ADD_MONTHS ( DATE ' 2025 - 02 - 04 ' , 1 , DATE ' 2025 - 06 - 05 ' ) feb4
FROM DUAL ;
次の出力が生成されます:
FEB1 FEB2 FEB3 FEB4
----------- ----------- ----------- -----------
04-MAR-2025 04-MAR-2025 04-MAR-2025 04-MAR-2025小売
小売関数も同様に定義されます。RETAIL_ADD_MONTHSは、前述の会計例と同様に、対応する日をその月の位置で戻します。is_restatedが'RESTATED'の場合、小売年にない日数がスキップされます。
例14-15
小売年2022の最終日は、2023年1月28日です。2023年は(53週間で)修正再表示された年であるため、次の例は、修正再表示された場合と修正再表示されていない場合における小売のRETAIL_ADD_DAYSの動作を示しています:
SELECT RETAIL_ADD_DAYS ( DATE ' 2023 - 01 - 28 ' , 1 , ' NOT RESTATED' ) not_res ,
RETAIL_ADD_DAYS ( DATE ' 2023 - 01 - 28 ' , 1 , 'RESTATED' ) res
FROM DUAL ;
次の出力が生成されます:
NOT_RES RES
----------- -----------
29-JAN-2023 05-FEB-2023修正再表示されていないバージョンでは、通常1月28日の後に1月29日が来ます。修正再表示されたバージョンでは、1月29日から2月4日は小売カレンダに存在しないため、次の有効な日は2月5日になります。
14.1.6 Since関数およびユーティリティ関数
Since関数
CALENDAR_SINCEは、指定された日付式とSYSDATE (たとえば、"2日前")の間の判読可能な間隔を計算します。粒度(日数、週数、月数など)は、間隔のサイズによって自動的に決定されます。
ノート:
現在、CALENDAR_SINCE問合せではグレゴリオ暦のみがサポートされています。
例14-16 CALENDAR_SINCE
この例は、CALENDAR_SINCEの使用方法を示しています:
SELECT CALENDAR_SINCE ( DATE ' 2025 - 03 - 15 ' , 'YYYY' ) YEAR
FROM DUAL ;
ユーティリティ関数: RETAIL_DAY_EXISTS
RETAIL_DAY_EXISTS(dtexpr, is_restated)は、指定された日付が指定された小売カレンダ・バリアントに存在する場合、TRUEを戻します。この関数は、修正再表示されたカレンダの使用時に小売関数へのコールをガードするのに役立ちます。
例14-17
この問合せでは、修正再表示された小売カレンダの有効な日数のみが含まれるため、日付が定義された小売年外にある場合のエラーを防止します:
SELECT RETAIL_MONTH ( order_date , ' DEFAULT ' , 'RESTATED' ) month ,
SUM ( sales ) sales
FROM fact
WHERE RETAIL_DAY_EXISTS ( order_date , 'RESTATED' ) = TRUE
GROUP BY ALL
ORDER BY 1 ;
14.1.7 ノートおよびチェックリスト
主なルールと制約
-
受け入れられる入力タイプ:
DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONEまたはDATEに暗黙的に変換可能な値。 -
書式文字列には、必要な指定子を含めて、関数の粒度で
DATEへのラウンドトリップ変換を許可する必要があります。4桁の年書式(YYYYまたはSYYYY)のみが許可されます。 -
会計パラメータ優先度: 会計開始日は28以前である必要があります。それ以外の場合はエラーが発生します。関数の引数
fis_year_startは、システム設定をオーバーライドするセッション・パラメータCALENDAR_FISCAL_YEAR_STARTをオーバーライドします。 -
小売カレンダは、NRF 4-5-4ルールに従います。
is_restatedは、修正再表示された動作を制御し、RETAIL_DAY_EXISTSは問合せをガードします。通常、小売年は52週あり、指定されたルールに従って53週目が追加されます。 -
リダクション:
FILTER WHERE変換とOracle Data Redactionが相互作用します。V1は、リダクション対応のCASE拡張パターンを使用して、Oracle Data Redactionで正確性を保持します。 -
互換性: データベースの互換性はバージョン26に設定する必要があります。関数は、PL/SQLブロック内で実行されるSQLで使用できます。
関数を使用する前のクイック・チェックリスト
-
入力が
DATEであるか、DATEに変換可能であることを確認します。 -
カレンダ・バリアント(カレンダ、会計または小売)を選択します。
-
会計の場合、
fis_year_startを渡すか、CALENDAR_FISCAL_YEAR_STARTを使用するかを決定します。 -
小売の場合は、
is_restatedを決定し、必要に応じてRETAIL_DAY_EXISTSを使用します。 -
書式文字列に必要な指定子が含まれていることを確認します(たとえば、四半期の場合は
YYYYおよびQ)。 -
月末、うるう年および小売の53週を中心にテストします。
-
リダクション時の動作を検証します(該当する場合)。
14.2 集計フィルタ
集計フィルタを使用すると、開発者はSUM、COUNT、AVGなどの集計関数で考慮される行を制限できます。これにより、追加の副問合せなしで、集計されるデータをより詳細に制御できます。
構文
aggregate_function ( aggregate_function_arguments ) [ FILTER ( WHERE condition )]-
aggregate_function: サポートされている任意の集計関数(たとえば、SUM、COUNT、AVG、MAX、MIN)。 -
aggregate_function_arguments: 集計する列または式。 -
condition: 集計に含める行を決定するブール述語
例14-18 条件を満たす行のみのカウント
次の例は、status列が'ACTIVE'と等しい行のみをカウントする方法を示しています。
SELECT COUNT(*) FILTER (WHERE status = 'ACTIVE') AS active_count
FROM employees;
例14-19 フィルタを使用した値の合計
次の例は、salaryの合計が'SALES'部門の従業員に対してのみどのように計算されるかを示しています。
SELECT SUM(salary) FILTER (WHERE department = 'SALES') AS sales_total
FROM employees;
例14-20 1つの問合せでフィルタされた複数の集計
次の例は、複数のフィルタされた集計を1つの問合せで組み合せて、異なる条件付き結果を生成する方法を示しています。
SELECT
COUNT(*) FILTER (WHERE status = 'ACTIVE') AS active_count,
COUNT(*) FILTER (WHERE status = 'INACTIVE') AS inactive_count
FROM employees;
例14-21 1つの問合せでフィルタされた複数の集計
その年の売上合計と、その年の最初の2四半期の売上と、その年の最後の2四半期の売上をすべて同じレポートに出力する問合せは、次のようにFILTER WHERE句を使用して記述できます:
SELECT
year,
SUM(sales) year_sales,
SUM(sales) FILTER (WHERE qtr_num IN (1, 2)) q1q2_sales,
SUM(sales) FILTER (WHERE qtr_num IN (3, 4)) q3q4_sales
FROM
sales_fact f LEFT OUTER JOIN
time_dim t ON (f.time_id = t.month_id)
GROUP BY year
ORDER BY yearノート
-
集計フィルタは、問合せの
WHERE句の後に評価されます。 -
これらは、条件付き集計ごとに個別の副問合せを記述する簡潔な代替手段を提供します。
-
フィルタは、サポートされている任意の集計関数に適用できます。
関連項目:
集計フィルタの構文およびセマンティックの詳細は、『Oracle AI Database SQL言語リファレンス』の集計関数を参照してください