{"id":1820,"date":"2018-04-26T23:10:22","date_gmt":"2018-04-26T14:10:22","guid":{"rendered":"http:\/\/www.dbsheetclient.jp\/blog\/?p=1820"},"modified":"2018-06-27T15:31:48","modified_gmt":"2018-06-27T06:31:48","slug":"over%e5%8f%a5%ef%bc%88oracle%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.dbsheetclient.jp\/blog\/?p=1820","title":{"rendered":"OVER\u53e5\uff08Oracle\uff09"},"content":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002<\/p>\n

\"IMG_1325\"<\/a><\/p>\n

4\u6708\u3082\u7d42\u76e4\u3092\u8fce\u3048\u3001\u3082\u3046\u3059\u3050\u30b4\u30fc\u30eb\u30c7\u30f3\u30a6\u30a4\u30fc\u30af\u3067\u3059\u306d\u3002
\u5148\u65e5\u3001\u57fc\u7389\u770c\u306e\u57fc\u7389\u770c\u6bd4\u4f01\u90e1\u6ed1\u5ddd\u753a\u306b\u3042\u308b\u3001\u56fd\u55b6\u6b66\u8535\u4e18\u9675\u68ee\u6797\u516c\u5712\u306b\u884c\u3063\u3066\u304d\u307e\u3057\u305f\u3002\u9577\u5e74\u57fc\u7389\u306b\u3044\u306a\u304c\u3089\u3001\u5b9f\u306f\u884c\u3063\u305f\u3053\u3068\u304c\u306a\u304b\u3063\u305f\u306e\u3067\u3059\u304c\u3001\u6771\u4eac\u30c9\u30fc\u30e065\u500b\u5206\u306e\u5e83\u3055\u304c\u3042\u308a\u3001\u96d1\u6728\u6797\u306b\u306f\u305f\u304f\u3055\u3093\u306e\u6728\u304c\u3042\u3063\u3066\u3001\u30b9\u30c8\u30ec\u30b9\u89e3\u6d88\u3001\u68ee\u6797\u6d74\u306b\u306f\u3082\u3063\u3066\u3053\u3044\u306e\u5834\u6240\u3060\u3068\u601d\u3044\u307e\u3057\u305f\u3002\u4e0a\u306e\u5199\u771f\u306f\u30cf\u30fc\u30d6\u30ac\u30fc\u30c7\u30f3\u3068\u3044\u3046\u30cf\u30fc\u30d6\u304c\u3044\u308d\u3044\u308d\u3068\u690d\u3048\u3089\u308c\u3066\u3044\u308b\u3068\u3053\u308d\u306e\u5199\u771f\u3067\u3059\u3002
\u4e0b\u306e\u5199\u771f\u306f\u3001\u30cd\u30e2\u30d5\u30a3\u30e9\u3068\u3044\u3046\u82b1\u304c\u54b2\u3044\u3066\u3044\u308b\u82b1\u7551\u3067\u3059\u3002<\/p>\n

\"IMG_1352\"<\/a><\/p>\n

\u65e5\u672c\u306b\u306f17\u500b\u304f\u3089\u3044\u306e\u56fd\u55b6\u306e\u516c\u5712\u304c\u3042\u308b\u306e\u3067\u3059\u304c\u3001\u3053\u3053\u304c\u4e00\u756a\u6700\u521d\u306b\u6574\u5099\u3055\u308c\u305f\u3068\u3053\u308d\u3060\u305d\u3046\u3067\u3059\u3002
\u5b63\u7bc0\u306b\u3088\u3063\u3066\u3044\u308d\u3044\u308d\u3068\u898b\u308c\u308b\u82b1\u3082\u3042\u308a\u3001\u30b5\u30a4\u30af\u30ea\u30f3\u30b0\u30b3\u30fc\u30b9\u3084\u5b50\u4f9b\u304c\u904a\u3079\u308b\u30ad\u30c3\u30ba\u30b3\u30fc\u30ca\u30fc\u3001\u65e5\u672c\u4e00\u5927\u304d\u306a\u30a8\u30a2\u30fc\u30c8\u30e9\u30f3\u30dd\u30ea\u30f3\u306a\u3069\u3001\u3044\u308d\u3044\u308d\u3068\u697d\u3057\u3081\u305d\u3046\u3067\u3059\u3002\u3086\u3063\u304f\u308a\u6b69\u3044\u305f\u306e\u3067\u3001\u4e00\u90e8\u3057\u304b\u56de\u308c\u307e\u305b\u3093\u3067\u3057\u305f\u304c\u3001\u4eca\u5ea6\u307e\u305f\u5bb6\u65cf\u3067\u884c\u3053\u3046\u304b\u3068\u601d\u3044\u307e\u3057\u305f\u3002
\u57fc\u7389\u306b\u3082\u3001\u63a2\u305b\u3070\u3044\u308d\u3093\u306a\u3044\u3044\u3068\u3053\u308d\u304c\u3042\u308a\u307e\u3059\u306d\u3002\u4eca\u307e\u3067\u5fd9\u3057\u3055\u306b\u304b\u307e\u3051\u3066\u884c\u304b\u306a\u304b\u3063\u305f\u306e\u304c\u672c\u5f53\u306b\u6b8b\u5ff5\uff01
\u5b50\u4f9b\u304c\u5c0f\u3055\u3044\u3068\u304d\u306b\u3001\u3082\u3063\u3068\u81ea\u7136\u3068\u89e6\u308c\u5408\u3046\u6a5f\u4f1a\u3092\u6301\u3064\u3088\u3046\u306b\u3059\u308c\u3070\u3088\u304b\u3063\u305f\u306d\u3001\u3068\u59bb\u3068\u8a71\u3092\u3059\u308b\u3053\u3068\u304c\u5897\u3048\u305f\u4eca\u65e5\u3053\u306e\u9803\u3067\u3059\u3002

\uff1c\u672c\u65e5\u306e\u984c\u6750\uff1e
<\/strong>OVER<\/strong>\u53e5\uff08Oracle<\/strong>\uff09
<\/strong>
\u524d\u56de<\/a>\u3001SQL Server\u306b\u95a2\u3057\u3066\u3001OVER\u53e5\u306eROWS\u3068\u3044\u3046\u3082\u306e\u3092\u984c\u6750\u306b\u53d6\u308a\u4e0a\u3052\u3066\u307f\u307e\u3057\u305f\u304c\u3001Oracle\u3067\u3082\u540c\u69d8\u306e\u3053\u3068\u306f\u3067\u304d\u308b\u306e\u304b\uff1f\u3068\u3044\u3046\u3053\u3068\u3092\u8abf\u3079\u3066\u307f\u305f\u3068\u3053\u308d\u3001\u4ee5\u524d\u304b\u3089\u3067\u304d\u308b\u3088\u3046\u306a\u306e\u3067\u3001\u4eca\u56de\u306fOracle\u3067\u78ba\u8a8d\u3057\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

Oracle\u3067\u3082OVER\u53e5\u3068\u4e00\u7dd2\u306b\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u53e5\u304c\u4f7f\u7528\u3067\u304d\u307e\u3059\u3002<\/p>\n

\u524d\u5f8c\u306e\u884c\u306e\u5024\u3092\u53d6\u5f97\u3059\u308b
LAG
— \u524d\u306e\u884c
LEAD
— \u5f8c\u306e\u884c<\/p>\n

\u524d\u56de\u306e\u4f8b\u3067\u3082\u898b\u305f\u79fb\u52d5\u5e73\u5747\u3084\u79fb\u52d5\u7d2f\u8a08\u306e\u53d6\u5f97
ROWS n PRECEDING
— \u73fe\u5728\u306e\u884c\u304b\u3089n\u884c\u524d\u3092\u542b\u3081\u3066\u5bfe\u8c61\u3068\u3059\u308b<\/p>\n

\u6700\u521d\u306e\u884c\u3001\u6700\u5f8c\u306e\u884c\u3001n\u756a\u76ee\u306e\u884c
FIRST_VALUE
— \u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u306e\u6700\u521d\u306e\u884c
LAST_VALUE
— \u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u306e\u6700\u5f8c\u306e\u884c
NTH_VALUE
— \u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u306eN\u756a\u76ee\u306e\u884c<\/p>\n

\u203b\u4e0a\u8a18\u3067\u3001IGNORE NULLS \u3068\u3044\u3046\u53e5\u3092\u4ed8\u3051\u308b\u3068\u3001NULL\u4ee5\u5916\u3067\u6700\u521d\u3001\u6700\u5f8c\u306a\u3069\u306e\u6307\u5b9a\u304c\u3067\u304d\u308b<\/p>\n

OVER\u53e5\u3084PARTITION BY\u53e5\u3092\u7d61\u3081\u305f\u66f8\u304d\u65b9\u3068\u3057\u3066\u306f\u3001<\/p>\n

LAG(\u9805\u76ee\u540d)
OVER (PARTITION BY \u9805\u76ee\u540d1
\u00a0 \u00a0 \u00a0ORDER BY \u9805\u76ee\u540d2
\u00a0 \u00a0 \u00a0)<\/p>\n

\u9805\u76ee\u540d1\u3054\u3068\u306b\u3001\u9805\u76ee\u540d2\u306e\u4e26\u3073\u9806\u3067\u3001\u9805\u76ee\u540d\u306e\u524d\u306e\u884c\u306e\u5024(LAG)\u3001\u9805\u76ee\u540d\u306e\u5f8c\u306e\u884c\u306e\u5024(LEAD)\u3092\u62bd\u51fa\u3057\u307e\u3059\u3002<\/p>\n

LAG(\u9805\u76ee\u540d)\u306e\u3068\u3053\u308d\u306f\u3001\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u8a2d\u5b9a\u304c\u53ef\u80fd
LEAD(\u9805\u76ee\u540d)
FIRST_VALUE(\u9805\u76ee\u540d)
LAST_VALUE(\u9805\u76ee\u540d)<\/p>\n

\u203bLAG\u3001LEAD\u306e\u4ee3\u308f\u308a\u306bSUM(\u9805\u76ee\u540d)\u3001MAX(\u9805\u76ee\u540d)\u306a\u3069\u3082\u4f7f\u3048\u307e\u3059\u3002<\/p>\n

\u4f8b\u3068\u3057\u3066\u3001\u524d\u56deSQL Server\u3067\u884c\u3063\u305f\u30aa\u30ea\u30f3\u30d4\u30c3\u30af\u306e\u30e1\u30c0\u30eb\u6570\u306e\u62bd\u51fa\u306e\u4f8b\u3092Oracle\u3067\u3082\u8a66\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

\u30c6\u30fc\u30d6\u30eb\u306f\u4ee5\u4e0b\u306e\u5b9a\u7fa9\uff1a
CREATE TABLE olympic_medal(
\u00a0season VARCHAR2(4)
,year\u00a0\u00a0 DECIMAL(4)
,color \u00a0VARCHAR(2)
,counts\u00a0\u00a0\u00a0\u00a0 INT
,CONSTRAINT pk_olympic_medal PRIMARY KEY (season, year, count));<\/p>\n

\u3053\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u3001\u590f\u5b63\u3001\u51ac\u5b63\u306e\u65e5\u672c\u304c\u53d6\u3063\u305f\u30e1\u30c0\u30eb\u6570\u3092\u767b\u9332\u3057\u3066\u307f\u307e\u3059\u3002\uff08\u524d\u56de\u3068\u540c\u69d8\u306b\u30c7\u30fc\u30bf\u3092Insert\u3057\u307e\u3059\u3002\u9014\u4e2d\u7701\u7565\uff09<\/p>\n

INSERT INTO olympic_medal VALUES(‘\u590f\u5b63’,1912,’\u91d1’,0);
INSERT INTO olympic_medal VALUES(‘\u590f\u5b63’,1912,’\u9280’,0);
INSERT INTO olympic_medal VALUES(‘\u590f\u5b63’,1912,’\u9285’,0);
\u2026
INSERT INTO olympic_medal VALUES(‘\u51ac\u5b63’,2018,’\u91d1’,4);
INSERT INTO olympic_medal VALUES(‘\u51ac\u5b63’,2018,’\u9280’,5);
INSERT INTO olympic_medal VALUES(‘\u51ac\u5b63’,2018,’\u9285’,4);<\/p>\n

\u590f\u5b63\u3001\u51ac\u5b63\u305d\u308c\u305e\u308c\u306e\u3001\u5404\u5e74\u6bce\u3001\u30e1\u30c0\u30eb\u8272\u6bce\u306e\u30e1\u30c0\u30eb\u6570\uff08\u524d\u56de\u3001\u6b21\u56de\u3082\u542b\u3081\u3066\uff09\u3068\u3001\u76f4\u8fd13\u56de\u306e\u30e1\u30c0\u30eb\u8272\u6bce\u306e\u5408\u8a08\u6570\u3001\u5e73\u5747\u6570\u3092\u62bd\u51fa\u3057\u3001\u5e74\u306e\u65b0\u3057\u3044\u9806\u306b\u62bd\u51fa\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

–\u307e\u305a\u51fa\u529b\u7d50\u679c\u306e\u30d5\u30a9\u30fc\u30de\u30c3\u30c8\u3092\u8a2d\u5b9a\u3057\u307e\u3059\u3002
col \u5b63\u7bc0 format a4
col \u5e74 format 9999
col \u8272 format a4
col \u4eca\u56de format 9999
col \u524d\u56de format 9999
col \u6b21\u56de format 9999
col \u76f4\u8fd13\u56de\u5408\u8a08 format 9999
col \u76f4\u8fd13\u56de\u5e73\u5747 format 9999<\/p>\n

–\u4ee5\u4e0b\u304cSQL
SELECT
\u00a0season \u5b63\u7bc0
,year \u5e74
,color \u8272
,counts \u4eca\u56de
,LAG(counts)
\u00a0 \u00a0OVER (PARTITION BY season, color
\u00a0 \u00a0 \u00a0ORDER BY year
\u00a0 \u00a0 \u00a0) AS \u524d\u56de
,LEAD(counts)
\u00a0 \u00a0OVER (PARTITION BY season, color
\u00a0 \u00a0 \u00a0ORDER BY year
\u00a0 \u00a0 \u00a0) AS \u6b21\u56de
,SUM(counts)
\u00a0 \u00a0OVER (PARTITION BY season, color
\u00a0 \u00a0 \u00a0ORDER BY year
\u00a0 \u00a0 \u00a0ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
\u00a0\u00a0 \u3000\u3000– \u73fe\u5728\u306e\u884c\u304b\u30892\u884c\u524d\u304b\u3089\u73fe\u5728\u884c\u307e\u3067\u306e3\u884c\u5206
\u00a0 \u00a0 \u00a0) AS \u76f4\u8fd13\u56de\u5408\u8a08
,AVG(counts)
\u00a0 \u00a0OVER (PARTITION BY season, color
\u00a0 \u00a0 \u00a0ORDER BY year
\u00a0 \u00a0 \u00a0ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
\u3000\u3000 — \u73fe\u5728\u306e\u884c\u304b\u30892\u884c\u524d\u304b\u3089\u73fe\u5728\u884c\u307e\u3067\u306e3\u884c\u5206
\u00a0 \u00a0 \u00a0) AS \u76f4\u8fd13\u56de\u5e73\u5747
FROM olympic_medal
ORDER BY season, year DESC, color;<\/p>\n

\"86_1\"<\/p>\n

<\/a><\/p>\n

\u6b21\u306b\u3001\u91d1\u3001\u9280\u3001\u9285\u3067\u8272\u3092\u5206\u3051\u305a\u306b\u3001\u5b63\u7bc0\u3001\u5e74\u6bce\u306e\u30e1\u30c0\u30eb\u6570\u306e\u5408\u8a08\u306b\u3064\u3044\u3066\u3001\u6700\u521d\u304b\u3089\u306e\u7d2f\u8a08\u306e\u5408\u8a08\u500b\u6570\u3068\u5e73\u5747\u500b\u6570\u3092\u62bd\u51fa\u3057\u3001\u51ac\u5b63\u3001\u590f\u5b63\u306e\u5e74\u306e\u65b0\u3057\u3044\u9806\u306b\u8868\u793a\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

col \u5b63\u7bc0 format a4
col \u5e74 format 9999
col \u8272 format a4
col \u4eca\u56de format 9999
col \u7d2f\u8a08\u306e\u5408\u8a08 format 9999
col \u7d2f\u8a08\u306e\u5e73\u5747 format 9999<\/p>\n

WITH C_YEAR_MEDAL(season, year, counts) AS
(SELECT season, year, SUM(counts)
\u00a0 \u00a0FROM olympic_medal
GROUP BY season, year
)
SELECT
season
,year
,counts
,SUM(counts)
\u00a0 \u00a0OVER (PARTITION BY season
\u00a0 \u00a0 \u00a0ORDER BY year
\u00a0 \u00a0 \u00a0ROWS BETWEEN UNBOUNDED PRECEDING
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0AND CURRENT ROW\u3000\u3000\u00a0\u00a0 — \u6700\u521d\u306e\u884c\u304b\u3089\u73fe\u5728\u884c\u307e\u3067
\u00a0 \u00a0 \u00a0) AS \u7d2f\u8a08\u306e\u5408\u8a08counts
,AVG(counts)
\u00a0 \u00a0OVER (PARTITION BY season
\u00a0 \u00a0 \u00a0ORDER BY year
\u00a0 \u00a0 \u00a0ROWS BETWEEN UNBOUNDED PRECEDING
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0AND CURRENT ROW\u00a0\u00a0 \u3000\u3000– \u6700\u521d\u306e\u884c\u304b\u3089\u73fe\u5728\u884c\u307e\u3067
\u00a0 \u00a0 \u00a0) AS \u7d2f\u8a08\u306e\u5e73\u5747counts
FROM C_YEAR_MEDAL
ORDER BY season DESC, year DESC;<\/p>\n

\"86_2\"<\/a><\/p>\n

\u4e0a\u8a18\u306e\u7d50\u679c\u304b\u3089\u3001SQL Server\u3068\u540c\u69d8\u306b\u3001OVER\u53e5\u3092\u4f7f\u7528\u3059\u308b\u3053\u3068\u3067\u3001\u5404\u884c\u306b\u5bfe\u3057\u3066\u306e\u76f4\u8fd1\u306e\u6570\u56de\u3084\u7d2f\u8a08\u306e\u5408\u8a08\u306a\u3069\u3092\u62bd\u51fa\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u308b\u3053\u3068\u304c\u78ba\u8a8d\u3067\u304d\u307e\u3057\u305f\u3002<\/p>\n

\u4eca\u65e5\u306f\u4ee5\u4e0a\u307e\u3067<\/p>\n","protected":false},"excerpt":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002 4\u6708\u3082\u7d42\u76e4\u3092\u8fce\u3048\u3001\u3082\u3046\u3059\u3050\u30b4\u30fc\u30eb\u30c7\u30f3\u30a6\u30a4\u30fc\u30af\u3067\u3059\u306d\u3002\u5148\u65e5\u3001\u57fc\u7389\u770c\u306e\u57fc\u7389\u770c\u6bd4\u4f01\u90e1\u6ed1\u5ddd\u753a\u306b\u3042\u308b\u3001\u56fd\u55b6\u6b66\u8535\u4e18\u9675\u68ee\u6797\u516c\u5712\u306b\u884c\u3063\u3066\u304d\u307e\u3057\u305f\u3002\u9577\u5e74\u57fc\u7389\u306b\u3044\u306a\u304c\u3089\u3001\u5b9f\u306f\u884c\u3063\u305f\u3053\u3068\u304c\u306a\u304b\u3063\u305f\u306e\u3067\u3059\u304c\u3001\u6771 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[9],"tags":[],"class_list":["post-1820","post","type-post","status-publish","format-standard","hentry","category-9"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1820","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1820"}],"version-history":[{"count":14,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1820\/revisions"}],"predecessor-version":[{"id":1907,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1820\/revisions\/1907"}],"wp:attachment":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1820"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1820"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1820"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}