{"id":633,"date":"2015-12-04T18:47:59","date_gmt":"2015-12-04T09:47:59","guid":{"rendered":"http:\/\/dlog.net-squares.com\/?p=633"},"modified":"2015-12-04T19:26:48","modified_gmt":"2015-12-04T10:26:48","slug":"%e3%83%80%e3%83%9f%e3%83%bc%e3%83%87%e3%83%bc%e3%82%bf%e3%81%ae%e4%bd%9c%e3%82%8a%e6%96%b9-%e3%82%b9%e3%82%b1%e3%82%b8%e3%83%a5%e3%83%bc%e3%83%ab%e3%83%87%e3%83%bc%e3%82%bf%e7%b7%a8","status":"publish","type":"post","link":"https:\/\/dlog.net-squares.com\/?p=633","title":{"rendered":"\u30c0\u30df\u30fc\u30c7\u30fc\u30bf\u306e\u4f5c\u308a\u65b9 \u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30c7\u30fc\u30bf\u7de8"},"content":{"rendered":"\r\n<p>\u30cd\u30c3\u30c8\u30b9\u30af\u30a8\u30a2\u306e\u6c38\u7530\u3067\u3059\u3002<\/p>\r\n<p>\u4eca\u56de\u306f\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u306e\u30c0\u30df\u30fc\u30c7\u30fc\u30bf\u3092\u4e00\u62ec\u4f5c\u6210\u3059\u308b\u65b9\u6cd5\u306b\u3064\u3044\u3066\u306e\u7d39\u4ecb\u3067\u3059\u3002<\/p>\r\n<p>\u3053\u3053\u3067\u306e\u300c\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u300d\u3068\u306f\u3001\u300c \u958b\u59cb\u65e5\u6642\u3068\u7d42\u4e86\u65e5\u6642\u3092\u6301\u3061\u3001\u7279\u5b9a\u306e\u30e6\u30fc\u30b6\u306b\u5c5e\u3059\u308b\u60c5\u5831 \u300d\u3068\u3044\u3046\u5b9a\u7fa9\u306b\u3057\u307e\u3059\u3002<br \/>\u30b0\u30eb\u30fc\u30d7\u30a6\u30a7\u30a2\u306e\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u60c5\u5831\u3084\u3001\u4e88\u7d04\u7ba1\u7406\u30b7\u30b9\u30c6\u30e0\u306b\u304a\u3051\u308b\u4e88\u7d04\u60c5\u5831\u306a\u3069\u3092\u601d\u3044\u6d6e\u304b\u3079\u3066\u3082\u3089\u3048\u305f\u3089\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\r\n<h3>[\u524d\u63d0\u6761\u4ef6]<\/h3>\r\n<p>\u4f7f\u7528\u3059\u308b\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\uff0f\u30c6\u30fc\u30d6\u30eb\u3001\u4f5c\u6210\u3059\u308b\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u306e\u5185\u5bb9\u306b\u3064\u3044\u3066\u306f\u4e0b\u8a18\u306e\u901a\u308a\u3068\u3057\u307e\u3059\u3002<\/p>\r\n<ul>\r\n\t<li>\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306fPostgreSQL 9.4<\/li>\r\n\t<li>\u30e6\u30fc\u30b6\uff08users\uff09\u3068\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\uff08schedules\uff09\u306e\u5404\u30c6\u30fc\u30d6\u30eb\u306e\u30ec\u30a4\u30a2\u30a6\u30c8\u306f\u4ee5\u4e0b\u306e\u901a\u308a\r\n<pre>create table users (\r\n    user_id serial not null         -- \u30e6\u30fc\u30b6\u30fcID\r\n  , name character varying(30)      -- \u540d\r\n  , constraint users_pk primary key (user_id)\r\n);\r\ncreate table schedules (\r\n    schedule_id serial not null     -- \u30b9\u30b1\u30b8\u30e5\u30fc\u30ebID\r\n  , user_id integer not null        -- \u30e6\u30fc\u30b6\u30fcID\r\n  , start_date timestamp not null   -- \u958b\u59cb\u65e5\u6642\r\n  , end_date timestamp not null     -- \u7d42\u4e86\u65e5\u6642\r\n  , constraint schedules_pk primary key (schedule_id)\r\n);<\/pre>\r\n<\/li>\r\n\t<li>\u30e6\u30fc\u30b6\u306e\u30c7\u30fc\u30bf\u306f\u4ee5\u4e0b\u306e3\u4ef6\u3092\u7528\u610f\r\n<pre>INSERT INTO users(name) VALUES('\u524d\u7530');\r\nINSERT INTO users(name) VALUES('\u5927\u702c\u826f');\r\nINSERT INTO users(name) VALUES('\u30b8\u30e7\u30f3\u30bd\u30f3');<\/pre>\r\n<\/li>\r\n\t<li>\r\n<pre>\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u306e\u4f5c\u6210\u5bfe\u8c61\u671f\u9593\u306f2015\/12\/1\uff5e2015\/12\/31\u3067\u3001\u5404\u65e59\u6642\u304b\u308918\u6642\u307e\u3067\u306e\u7bc4\u56f2\u306730\u5206\u9593\u9694\u306b\u6577\u304d\u8a70\u3081\u308b\u3088\u3046\u306b\u4f5c\u6210<\/pre>\r\n<\/li>\r\n\t<li>\r\n<pre>\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u306f\u5168\u30e6\u30fc\u30b6\u306b\u5bfe\u3057\u3066\u4f5c\u6210<\/pre>\r\n<\/li>\r\n<\/ul>\r\n<h3>[\u4f5c\u6210\u65b9\u6cd5]<\/h3>\r\n<p>\u30c7\u30fc\u30bf\u306e\u4f5c\u6210\u306b\u306f<a href=\"https:\/\/www.postgresql.jp\/document\/9.4\/html\/plpgsql.html\">PL\/pgSQL<\/a>\u3092\u5229\u7528\u3057\u3001\u30b3\u30fc\u30c9\u306e\u5b9f\u884c\u306b\u3064\u3044\u3066\u306fWindows\u74b0\u5883\u306e<a href=\"http:\/\/www.pgadmin.org\/\">pgAdmin III<\/a>\u3092\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\r\n<p>\u30c7\u30fc\u30bf\u4f5c\u6210\u3092\u884c\u3046\u30b3\u30fc\u30c9\u306f\u4e0b\u8a18\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\r\n<pre style=\"border: 1px solid #ccc;overflow: auto;padding: 0 5px\">DO $$\r\nDECLARE\r\n  d   DATE;       -- \u65e5\u4ed8\r\n  t   TIMESTAMP;  -- \u6642\u523b\r\n  u   RECORD;     -- \u30e6\u30fc\u30b6\u306e\u30ec\u30b3\u30fc\u30c9\r\nBEGIN\r\n <strong>-- \u2460 12\/1\u304b\u308912\/31\u307e\u30671\u65e5\u3054\u3068\u306e\u30eb\u30fc\u30d7<\/strong>\r\n FOR d IN SELECT generate_series('2015-12-01'::DATE, '2015-12-31'::DATE, '1 day') AS d LOOP \r\n   <strong>-- \u2461 9\u6642\u304b\u308917\u6642\u534a\u307e\u306730\u5206\u3054\u3068\u306e\u30eb\u30fc\u30d7<\/strong>\r\n   FOR t IN SELECT generate_series(d + interval '9 hour', d + interval '17 hour 30 minute', '30 minute') AS t LOOP\r\n     <strong>-- \u2462 \u30e6\u30fc\u30b61\u4eba\u3054\u3068\u306e\u30eb\u30fc\u30d7<\/strong>\r\n     FOR u IN SELECT * FROM users ORDER BY user_id LOOP\r\n       <strong>-- \u2463 INSERT\u6587\u306e\u5b9f\u884c<\/strong>\r\n       EXECUTE 'INSERT INTO schedules (user_id, start_date, end_date) VALUES (' || u.user_id || ',''' || t || ''', ''' || t + interval '30 minute' || ''')';\r\n     END LOOP;\r\n   END LOOP;\r\n END LOOP;\r\nEND$$ language plpgsql;<\/pre>\r\n<p><br \/>\u4e0a\u8a18\u306e\u30b3\u30fc\u30c9\u3092\u7c21\u5358\u306b\u89e3\u8aac\u3059\u308b\u3068\u3001<br \/>\u2460\u3067generate_series\u95a2\u6570\u306712\/1\u304b\u308912\/31\u307e\u3067\u306e\u30ec\u30b3\u30fc\u30c9\u3092\u4f5c\u6210\u3057\u3066\u30eb\u30fc\u30d7\u3001<br \/>\u2461\u3067generate_series\u95a2\u6570\u3068interval\u3092\u4f7f\u7528\u3057\u3066\u5404\u65e5\u306e9\u6642\u304b\u308917\u6642\u534a\u307e\u3067\u306e\u30ec\u30b3\u30fc\u30c9\u3092\u4f5c\u6210\u3057\u3066\u30eb\u30fc\u30d7\u3001<br \/>\u2462\u3067\u5168\u30e6\u30fc\u30b6\u3092\u53d6\u5f97\u3057\u3066\u30eb\u30fc\u30d7\u3001<br \/>\u2463\u3067\u958b\u59cb\u6642\u523b\u306f\u2461\u306e\u6642\u523b\u306b\u3057\u3066\u7d42\u4e86\u6642\u523b\u306f\u2461\u306e\u6642\u523b\u306e30\u5206\u5f8c\u306b\u3057\u305f\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30c7\u30fc\u30bf\u3092INSERT<br \/>\u3068\u3044\u3046\u3053\u3068\u3092\u3084\u3063\u3066\u3044\u307e\u3059\u3002<\/p>\r\n<p>\u00a0\u3053\u306e\u30b3\u30fc\u30c9\u306e\u5b9f\u884c\u30671674\u4ef6\uff0831\u65e5 \u00d7 9\u6642\u9593 \u00d7 2\u5206\u5272 \u00d7 3\u4eba)\u306e\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30c7\u30fc\u30bf\u304c\u4f5c\u6210\u3067\u304d\u307e\u3059\u3002<\/p>\r\n<p>\u4e0a\u8a18\u306e\u30b3\u30fc\u30c9\u3067\u306f\u9699\u9593\u306a\u304f\u6577\u304d\u8a70\u3081\u308b\u3088\u3046\u306b\u30c7\u30fc\u30bf\u3092\u4f5c\u6210\u3057\u307e\u3057\u305f\u304c\u3001IF\u6587\u3092\u8ffd\u52a0\u3059\u308b\u3053\u3068\u3067<br \/>\u2460\u306e\u30eb\u30fc\u30d7\u3067\u571f\u65e5\u306f\u30b9\u30ad\u30c3\u30d7\u3055\u305b\u305f\u308a\u3001\u2461\u306e\u30eb\u30fc\u30d7\u3067\u663c\u4f11\u307f\u306e\u6642\u9593\u306b\u3064\u3044\u3066\u306f\u30b9\u30ad\u30c3\u30d7\u3055\u305b\u305f\u308a\u3001<br \/>random\u95a2\u6570\u3092\u4f7f\u3063\u3066\u95be\u5024\u672a\u6e80\u306e\u5834\u5408\u306f\u30b9\u30ad\u30c3\u30d7\u3057\u3066\u4f5c\u6210\u9593\u9694\u3092\u307e\u3070\u3089\u306b\u3055\u305b\u305f\u308a\u3001<br \/>\u3068\u3044\u3063\u305f\u30b3\u30f3\u30c8\u30ed\u30fc\u30eb\u304c\u3067\u304d\u308b\u304b\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\r\n<p>\u4ee5\u4e0a\u3001\u4eca\u56de\u306f\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30c7\u30fc\u30bf\u3092\u984c\u6750\u306b\u3057\u307e\u3057\u305f\u304c\u3001<br \/>PL\/pgSQL\u3092\u4f7f\u3046\u3068\u624b\u7d9a\u304d\u51e6\u7406\u304c\u3067\u304d\u308b\u306e\u3067\u3001\u4ed6\u306e\u30c7\u30fc\u30bf\u306e\u4f5c\u6210\u306b\u3082\u5fdc\u7528\u3067\u304d\u308b\u3068\u601d\u3044\u307e\u3059\u3002<br \/>\u305c\u3072\u8272\u3005\u3068\u8a66\u3057\u3066\u307f\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\r\n","protected":false},"excerpt":{"rendered":"<p>\u30cd\u30c3\u30c8\u30b9\u30af\u30a8\u30a2\u306e\u6c38\u7530\u3067\u3059\u3002 \u4eca\u56de\u306f\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u306e\u30c0\u30df\u30fc\u30c7\u30fc\u30bf\u3092\u4e00\u62ec\u4f5c\u6210\u3059\u308b\u65b9\u6cd5\u306b\u3064\u3044\u3066\u306e\u7d39\u4ecb\u3067\u3059\u3002 \u3053\u3053\u3067\u306e\u300c\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u300d\u3068\u306f\u3001\u300c \u958b\u59cb\u65e5\u6642\u3068\u7d42\u4e86\u65e5\u6642\u3092\u6301\u3061\u3001\u7279\u5b9a\u306e\u30e6\u30fc\u30b6\u306b\u5c5e\u3059\u308b\u60c5\u5831 \u300d\u3068\u3044\u3046\u5b9a\u7fa9\u306b\u3057\u307e\u3059\u3002\u30b0\u30eb\u30fc\u30d7\u30a6\u30a7 [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-633","post","type-post","status-publish","format-standard","hentry","category-3"],"_links":{"self":[{"href":"https:\/\/dlog.net-squares.com\/index.php?rest_route=\/wp\/v2\/posts\/633","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dlog.net-squares.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dlog.net-squares.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dlog.net-squares.com\/index.php?rest_route=\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/dlog.net-squares.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=633"}],"version-history":[{"count":29,"href":"https:\/\/dlog.net-squares.com\/index.php?rest_route=\/wp\/v2\/posts\/633\/revisions"}],"predecessor-version":[{"id":661,"href":"https:\/\/dlog.net-squares.com\/index.php?rest_route=\/wp\/v2\/posts\/633\/revisions\/661"}],"wp:attachment":[{"href":"https:\/\/dlog.net-squares.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=633"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dlog.net-squares.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=633"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dlog.net-squares.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=633"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}