{"id":609,"date":"2018-05-22T06:27:30","date_gmt":"2018-05-21T21:27:30","guid":{"rendered":"https:\/\/www.oqiita.com\/?p=609"},"modified":"2018-06-16T16:48:01","modified_gmt":"2018-06-16T07:48:01","slug":"post-609","status":"publish","type":"post","link":"https:\/\/www.oqiita.com\/?p=609","title":{"rendered":"\u3010DB\u3011ORACLE \u306e GROUP BY \u3068\u304b PARTITION BY"},"content":{"rendered":"

\u3053\u308c\u3082\u5099\u5fd8\u9332\u3068\u3057\u3066\u3002<\/p>\n


\n<\/p>\n

\u60f3\u5b9a\u30c7\u30fc\u30bf<\/h3>\n
SELECT\n  ID,\n  NAME,\n  KBN,\n  PRICE\nFROM \n  TRANSACTION1\n;\n<\/code><\/pre>\n
\"\"<\/a><\/p>\n
\u533a\u5206\u304cKBN\u3063\u3066\u30c0\u30b5\u3044\u3088\u306d\u30fb\u30fb\u30fb\uff57<\/del><\/p>\n
\u533a\u5206\uff08KBN\uff09\u3054\u3068\u306b\u307e\u3068\u3081\u308b<\/h3>\n
SELECT\n  -- ID,\n  -- NAME,\n  KBN,\n  SUM(PRICE),\n  MAX(PRICE),\n  MIN(PRICE)\nFROM \n  TRANSACTION1\nGROUP BY KBN\nORDER BY KBN\n;\n<\/code><\/pre>\n
\"\"<\/a><\/p>\n
\u533a\u5206\uff08KBN\uff09\u3054\u3068\u306bROW_NUMBER\u3092\u3064\u3051\u308b<\/h3>\n
\u3044\u3064\u3082\u5fd8\u308c\u308b\u300cPARTITION BY\u300d\u3002<\/p>\n
SELECT\n  ID,\n  NAME,\n  KBN,\n  PRICE,\n  ROW_NUMBER() OVER(PARTITION BY KBN ORDER BY PRICE ASC) ROW_NUM\nFROM \n  TRANSACTION1\nORDER BY ID\n;\n<\/code><\/pre>\n
\"\"<\/a><\/p>\n
\u5148\u982d\uff12\u4ef6\u3060\u3051\u53d6\u5f97<\/h3>\n
SELECT *\nFROM(\n    SELECT\n      ID,\n      NAME,\n      KBN,\n      PRICE,\n      ROW_NUMBER() OVER(PARTITION BY KBN ORDER BY PRICE ASC) ROW_NUM\n    FROM \n      TRANSACTION1\n    )\nWHERE ROW_NUM <= 2\nORDER BY ID\n;\n<\/code><\/pre>\n
\"\"<\/a><\/p>\n
\u6a2a\u3067\u53d6\u5f97<\/h3>\n
SELECT \n  T1.KBN,\n  MAX(CASE WHEN T1.ROW_NUM = 1 THEN T1.PRICE END) AS ROW1,\n  MAX(CASE WHEN T1.ROW_NUM = 2 THEN T1.PRICE END) AS ROW2,\n  MAX(CASE WHEN T1.ROW_NUM = 3 THEN T1.PRICE END) AS ROW3\nFROM (\n  SELECT \n    KBN,\n    PRICE,\n    ROW_NUMBER() OVER(PARTITION BY KBN ORDER BY PRICE ASC) ROW_NUM\n  FROM \n    TRANSACTION1\n)T1\nGROUP BY T1.KBN\n;\n<\/code><\/pre>\n
\"\"<\/a><\/p>\n
\n
\u3068\u3044\u3046\u3053\u3068\u3067\u3061\u3087\u3044\u9069\u5f53\u306aSQL\u306b\u306a\u3063\u3066\u3057\u307e\u3063\u305f\u304c\u3001\u307e\u3068\u3081\u305f\u3044\u60c5\u5831\u306f\u307e\u3068\u3081\u305f\u304b\u306a\u3002
\n\u4ee5\u4e0a\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u3053\u308c\u3082\u5099\u5fd8\u9332\u3068\u3057\u3066\u3002<\/p>\n","protected":false},"author":1,"featured_media":733,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[90],"tags":[91,98,92,99,103,97,102,101,100],"class_list":{"0":"post-609","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-db","8":"tag-db","9":"tag-group-by","10":"tag-oracle","11":"tag-partition-by","12":"tag-row_number","13":"tag-97","14":"tag-102","15":"tag-101","16":"tag-100","17":"entry"},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.oqiita.com\/wp-content\/uploads\/2018\/05\/sango_db_icatch.png","jetpack_shortlink":"https:\/\/wp.me\/p8QluP-9P","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts\/609","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=609"}],"version-history":[{"count":15,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts\/609\/revisions"}],"predecessor-version":[{"id":906,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts\/609\/revisions\/906"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/media\/733"}],"wp:attachment":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=609"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=609"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=609"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}