{"id":6624,"date":"2025-05-16T15:49:10","date_gmt":"2025-05-16T06:49:10","guid":{"rendered":"https:\/\/blog.since2020.jp\/?p=6624"},"modified":"2025-05-16T15:49:10","modified_gmt":"2025-05-16T06:49:10","slug":"query_optimization","status":"publish","type":"post","link":"https:\/\/since2020.jp\/media\/query_optimization\/","title":{"rendered":"SQL\u30af\u30a8\u30ea\u6700\u9069\u5316\u5165\u9580"},"content":{"rendered":"\n<p>SQL\u30af\u30a8\u30ea\u6700\u9069\u5316\u306e\u57fa\u672c\u30c6\u30af\u30cb\u30c3\u30af\u3092\u5b66\u3073\u3001\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u5411\u4e0a\u3092\u76ee\u6307\u3057\u307e\u3057\u3087\u3046\u3002SELECT * \u306e\u56de\u907f\u3084\u3001JOIN\u306e\u6700\u9069\u5316\u3001\u30b5\u30d6\u30af\u30a8\u30ea\u306e\u4ee3\u66ff\u65b9\u6cd5\u3001\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u5206\u5272\u30fb\u30af\u30e9\u30b9\u30bf\u30ea\u30f3\u30b0\u306e\u6d3b\u7528\u65b9\u6cd5\u3092\u89e3\u8aac\u3057\u307e\u3059\u3002<\/p>\n\n\n<h2>\u30af\u30a8\u30ea\u6700\u9069\u5316\u3068\u306f<\/h2>\n<p>SQL\u30af\u30a8\u30ea\u6700\u9069\u5316\u3068\u306f\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u5bfe\u3057\u3066\u767a\u884c\u3055\u308c\u308bSQL\u6587\u306e\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u3092\u5411\u4e0a\u3055\u305b\u308b\u6280\u8853\u3084\u624b\u6cd5\u306e\u3053\u3068\u3092\u6307\u3057\u307e\u3059\u3002\u5927\u91cf\u306e\u30c7\u30fc\u30bf\u3092\u52b9\u7387\u7684\u306b\u51e6\u7406\u3057\u3001\u5fdc\u7b54\u6642\u9593\u3092\u77ed\u7e2e\u3059\u308b\u3053\u3068\u3067\u3001\u30b7\u30b9\u30c6\u30e0\u5168\u4f53\u306e\u8ca0\u8377\u3092\u8efd\u6e1b\u3057\u3001\u30e6\u30fc\u30b6\u30fc\u4f53\u9a13\u3092\u5411\u4e0a\u3055\u305b\u308b\u3053\u3068\u304c\u76ee\u7684\u3067\u3059\u3002<!-- notionvc: 9aec96c7-690b-4b54-8153-711860884988 --><\/p>\n\n<h2>\u4e3b\u8981\u306a\u30af\u30a8\u30ea\u6700\u9069\u5316\u30c6\u30af\u30cb\u30c3\u30af<\/h2>\n<p><strong>\u3010SELECT * \u306e\u4f7f\u7528\u3092\u907f\u3051\u308b\u3011<\/strong><\/p>\r\n<p>SELECT * \u306f\u30c6\u30fc\u30d6\u30eb\u5185\u306e\u5168\u30ab\u30e9\u30e0\u3092\u53d6\u5f97\u3059\u308b\u305f\u3081\u3001\u5fc5\u8981\u4ee5\u4e0a\u306e\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3057\u3001\u51e6\u7406\u901f\u5ea6\u3084\u901a\u4fe1\u30b3\u30b9\u30c8\u304c\u60aa\u5316\u3057\u307e\u3059\u3002 \u5fc5\u8981\u306a\u30ab\u30e9\u30e0\u3060\u3051\u3092\u660e\u793a\u7684\u306b\u6307\u5b9a\u3057\u307e\u3057\u3087\u3046\u3002<\/p>\r\n<pre><code class=\"language-sql\">-- NG\r\nSELECT * FROM orders;\r\n\r\n-- OK\r\nSELECT order_id, order_date, customer_id FROM orders;\r\n<\/code><\/pre>\r\n<p>&nbsp;<\/p>\r\n<p><strong>\u3010JOIN\u306f\u5c0f\u898f\u6a21\u306a\u30c6\u30fc\u30d6\u30eb\u3067\u5b9f\u884c\u3059\u308b\u3011<\/strong><\/p>\r\n<p>JOIN\u3092\u884c\u3046\u7d50\u5408\u306f\u3001\u7d50\u5408\u5bfe\u8c61\u306e\u30c6\u30fc\u30d6\u30eb\u304c\u5927\u304d\u3044\u3068\u51e6\u7406\u304c\u91cd\u304f\u306a\u308a\u307e\u3059\u3002 \u53ef\u80fd\u306a\u9650\u308a\u30d5\u30a3\u30eb\u30bf\u30ea\u30f3\u30b0\u3084\u96c6\u7d04\u3092\u884c\u3044\u3001\u5c0f\u898f\u6a21\u306a\u30c7\u30fc\u30bf\u540c\u58eb\u3092\u7d50\u5408\u3057\u307e\u3057\u3087\u3046\u3002<\/p>\r\n<pre><code class=\"language-sql\">-- \u975e\u52b9\u7387\u306a\u4f8b\uff08\u5927\u304d\u306a\u30c6\u30fc\u30d6\u30eb\u540c\u58eb\u3092\u7d50\u5408\uff09\r\nSELECT o.order_id, c.customer_name\r\nFROM orders o\r\nJOIN customers c ON o.customer_id = c.customer_id;\r\n\r\n-- \u52b9\u7387\u5316\u4f8b\uff08\u6ce8\u6587\u30c7\u30fc\u30bf\u3092\u30d5\u30a3\u30eb\u30bf\u3057\u3066\u304b\u3089\u7d50\u5408\uff09\r\nWITH recent_orders AS (\r\n  SELECT * FROM orders WHERE order_date &gt; DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)\r\n)\r\nSELECT ro.order_id, c.customer_name\r\nFROM recent_orders ro\r\nJOIN customers c ON ro.customer_id = c.customer_id;\r\n<\/code><\/pre>\r\n<p>&nbsp;<\/p>\r\n<p><strong>\u3010\u30b5\u30d6\u30af\u30a8\u30ea\u306e\u4f7f\u7528\u3092\u63a7\u3048\u308b\u3011<\/strong><\/p>\r\n<p>\u30b5\u30d6\u30af\u30a8\u30ea\u306f\u4fbf\u5229\u3067\u3059\u304c\u3001\u904e\u5ea6\u306b\u4f7f\u3046\u3068\u30af\u30a8\u30ea\u304c\u8907\u96d1\u306b\u306a\u308a\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u304c\u843d\u3061\u308b\u3053\u3068\u304c\u3042\u308a\u307e\u3059\u3002 JOIN\u3084\u5171\u901a\u30c6\u30fc\u30d6\u30eb\u5f0f\uff08CTE\uff09\u3067\u4ee3\u66ff\u3059\u308b\u3053\u3068\u3092\u691c\u8a0e\u3057\u307e\u3057\u3087\u3046\u3002<\/p>\r\n<pre><code class=\"language-sql\">-- \u30b5\u30d6\u30af\u30a8\u30ea\uff08\u975e\u63a8\u5968\u4f8b\uff09\r\nSELECT user_id, total_sales\r\nFROM (\r\n  SELECT user_id, SUM(amount) AS total_sales\r\n  FROM orders\r\n  GROUP BY user_id\r\n) sub\r\nWHERE total_sales &gt; 1000;\r\n\r\n-- CTE\u3092\u4f7f\u3063\u305f\u4f8b\uff08\u63a8\u5968\uff09\r\nWITH user_sales AS (\r\n  SELECT user_id, SUM(amount) AS total_sales\r\n  FROM orders\r\n  GROUP BY user_id\r\n)\r\nSELECT user_id, total_sales\r\nFROM user_sales\r\nWHERE total_sales &gt; 1000;\r\n<\/code><\/pre>\r\n<p>&nbsp;<\/p>\r\n<p>\u3010\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u5206\u5272\u3092\u884c\u3046\u3011<\/p>\r\n<p>\u30c6\u30fc\u30d6\u30eb\u3092\u65e5\u4ed8\u3084\u7bc4\u56f2\u3067\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u5206\u5272\u3059\u308b\u3053\u3068\u3067\u3001\u30af\u30a8\u30ea\u306e\u5bfe\u8c61\u30c7\u30fc\u30bf\u3092\u7d5e\u308a\u8fbc\u307f\u9ad8\u901f\u5316\u3067\u304d\u307e\u3059\u3002<\/p>\r\n<pre><code class=\"language-sql\">-- \u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u4f8b\uff08BigQuery\uff09\r\nCREATE TABLE sales_data_partitioned (\r\n  order_id INT64,\r\n  order_date DATE,\r\n  amount FLOAT64\r\n)\r\nPARTITION BY order_date;\r\n\r\n-- \u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u3092\u6d3b\u7528\u3057\u305f\u30af\u30a8\u30ea\u4f8b\r\nSELECT order_id, amount\r\nFROM sales_data_partitioned\r\nWHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';\r\n<\/code><\/pre>\r\n<p>&nbsp;<\/p>\r\n<p>\u3010\u30af\u30e9\u30b9\u30bf\u5316\u3092\u884c\u3046\u3011<\/p>\r\n<p>\u30af\u30e9\u30b9\u30bf\u30ea\u30f3\u30b0\u306f\u3001\u7279\u5b9a\u30ab\u30e9\u30e0\u306e\u5024\u3054\u3068\u306b\u30c7\u30fc\u30bf\u3092\u307e\u3068\u3081\u3066\u683c\u7d0d\u3057\u3001\u691c\u7d22\u3084\u96c6\u8a08\u306e\u52b9\u7387\u3092\u4e0a\u3052\u307e\u3059\u3002<\/p>\r\n<pre><code class=\"language-sql\">-- \u30af\u30e9\u30b9\u30bf\u30ea\u30f3\u30b0\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u4f8b\uff08BigQuery\uff09\r\nCREATE TABLE clustered_sales_data\r\nCLUSTER BY customer_id, product_id AS\r\nSELECT * FROM sales_data;\r\n\r\n-- \u30af\u30e9\u30b9\u30bf\u30ea\u30f3\u30b0\u3055\u308c\u305f\u30ab\u30e9\u30e0\u3067\u306e\u691c\u7d22\u304c\u9ad8\u901f\u5316\r\nSELECT customer_id, SUM(amount)\r\nFROM clustered_sales_data\r\nWHERE customer_id = 'CUST123'\r\nGROUP BY customer_id;<\/code><\/pre>\r\n<p><!-- notionvc: 7f778ca4-ab28-4827-8326-a0dd925bbbcf --><\/p>\n\n<h2>\u307e\u3068\u3081<\/h2>\n<p>SQL\u30af\u30a8\u30ea\u6700\u9069\u5316\u306f\u3001\u52b9\u7387\u7684\u306a\u30c7\u30fc\u30bf\u51e6\u7406\u3068\u30b7\u30b9\u30c6\u30e0\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u306e\u5411\u4e0a\u306b\u4e0d\u53ef\u6b20\u3067\u3059\u3002\u4eca\u56de\u7d39\u4ecb\u3057\u305f\u30c6\u30af\u30cb\u30c3\u30af\u306f\u3069\u308c\u3082\u57fa\u672c\u7684\u304b\u3064\u52b9\u679c\u7684\u306a\u65b9\u6cd5\u306a\u306e\u3067\u3001\u305c\u3072\u666e\u6bb5\u306e\u958b\u767a\u3084\u5206\u6790\u306b\u53d6\u308a\u5165\u308c\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\r\n<p><!-- notionvc: 8e4065a3-3323-4ca2-bd93-477c6c74552d --><\/p>","protected":false},"excerpt":{"rendered":"<p>SQL\u30af\u30a8\u30ea\u6700\u9069\u5316\u306e\u57fa\u672c\u30c6\u30af\u30cb\u30c3\u30af\u3092\u5b66\u3073\u3001\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u5411\u4e0a\u3092\u76ee\u6307\u3057\u307e\u3057\u3087\u3046\u3002SELECT * \u306e\u56de\u907f\u3084\u3001JOIN\u306e\u6700\u9069\u5316\u3001\u30b5\u30d6\u30af\u30a8\u30ea\u306e\u4ee3\u66ff\u65b9\u6cd5\u3001\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u5206\u5272\u30fb\u30af\u30e9\u30b9\u30bf\u30ea\u30f3\u30b0\u306e\u6d3b\u7528\u65b9\u6cd5\u3092\u89e3\u8aac\u3057\u307e\u3059\u3002 \u30af\u30a8\u30ea\u6700\u9069\u5316\u3068 [&hellip;]<\/p>\n","protected":false},"author":89,"featured_media":5351,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"content-type":"","swell_btn_cv_data":"","footnotes":"","_wp_rev_ctl_limit":""},"categories":[1246],"tags":[108,148,29],"class_list":["post-6624","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-infrastructure","tag-bigquery","tag-sql","tag-29"],"_links":{"self":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/posts\/6624","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/users\/89"}],"replies":[{"embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/comments?post=6624"}],"version-history":[{"count":0,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/posts\/6624\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/media\/5351"}],"wp:attachment":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/media?parent=6624"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/categories?post=6624"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/tags?post=6624"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}