{"id":14767,"date":"2021-10-21T06:00:00","date_gmt":"2021-10-20T21:00:00","guid":{"rendered":"https:\/\/www.muscle-hypertrophy.com\/?p=14767"},"modified":"2021-10-15T10:39:30","modified_gmt":"2021-10-15T01:39:30","slug":"sql-server-%e3%81%ae-lag-%e9%96%a2%e6%95%b0%e3%81%a7-1-%e8%a1%8c%e5%89%8d%e3%81%ae%e8%a1%8c%e3%82%92%e5%8f%96%e5%be%97%e3%81%99%e3%82%8b","status":"publish","type":"post","link":"https:\/\/www.muscle-hypertrophy.com\/?p=14767","title":{"rendered":"SQL Server \u306e LAG \u95a2\u6570\u3067 1 \u884c\u524d\u306e\u884c\u3092\u53d6\u5f97\u3059\u308b"},"content":{"rendered":"\n<p>\u3000<a href=\"https:\/\/www.muscle-hypertrophy.com\/?p=14746\" target=\"_blank\" rel=\"noopener\">SQL Server \u3067\u30a6\u30a3\u30f3\u30c9\u30a6\u95a2\u6570\u3092\u4f7f\u3044\uff0c1\u884c\u524d\u306e\u884c\u3092\u53d6\u5f97\u3059\u308b<\/a>\u3067\u306f\u30a6\u30a3\u30f3\u30c9\u30a6\u95a2\u6570\u3092\u7528\u3044\u30661\u884c\u524d\u306e\u884c\u3092\u53d6\u5f97\u3057\u305f\uff0e\u4eca\u56de\u306f LAG \u95a2\u6570\u3092\u7528\u3044\u30661\u884c\u524d\u306e\u884c\u3092\u53d6\u5f97\u3059\u308b\uff0e<\/p>\n<p><!--more--><\/p>\n<h2>EMERGENCYDB \u306e Temperature \u30c6\u30fc\u30d6\u30eb\u5b9a\u7fa9<\/h2>\n<pre>USE [EMERGENCYDB]\nGO\nCREATE TABLE [dbo].[Temperature](\n\t[\u5e74\u6708\u65e5] [date] NOT NULL,\n\t[\u90fd\u9053\u5e9c\u770c] [nvarchar](50) NOT NULL,\n\t[\u65e5\u5225\u6700\u9ad8\u6c17\u6e29] [float] NOT NULL,\n\t[\u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9] [nvarchar](2) NOT NULL\n) ON [PRIMARY]\nGO<\/pre>\n<h2>\u30af\u30a8\u30ea<\/h2>\n<p>\u3000\u4e0b\u8a18\u30af\u30a8\u30ea\u3092\u767a\u884c\u3057\u3066\u7d50\u679c\u3092\u5f97\u308b\uff0eLAG \u95a2\u6570\u306f\u524d\u306e\u884c\u3092\uff0cLEAD \u95a2\u6570\u306f\u5f8c\u306e\u884c\u3092\u53d6\u5f97\u3059\u308b\uff0e<\/p>\n<pre>USE EMERGENCYDB;\nGO\nSELECT\t\n\t\u5e74\u6708\u65e5\n,\t\u90fd\u9053\u5e9c\u770c\n,\tLAG(\u65e5\u5225\u6700\u9ad8\u6c17\u6e29, 1)\tOVER (PARTITION BY \u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9\tORDER BY \u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9, \u5e74\u6708\u65e5)\tAS\t\u524d\u65e5\u6700\u9ad8\u6c17\u6e29\n,\t\u65e5\u5225\u6700\u9ad8\u6c17\u6e29\n,\tLEAD(\u65e5\u5225\u6700\u9ad8\u6c17\u6e29,1)\tOVER (PARTITION BY \u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9\tORDER BY \u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9, \u5e74\u6708\u65e5)\tAS\t\u7fcc\u65e5\u6700\u9ad8\u6c17\u6e29\nFROM\tdbo.Temperature<\/pre>\n<pre>(111708 \u884c\u51e6\u7406\u3055\u308c\u307e\u3057\u305f)<\/pre>\n<h2>\u7d50\u679c<\/h2>\n<p>\u3000\u7d50\u679c\u304c\u53d6\u5f97\u3055\u308c\u3066\u3044\u308b\uff0e\u6700\u521d\u306e5\u884c\u3060\u3051\u793a\u3059\uff0e<\/p>\n<pre>\u5e74\u6708\u65e5\t\u90fd\u9053\u5e9c\u770c\t\u524d\u65e5\u6700\u9ad8\u6c17\u6e29\t\u65e5\u5225\u6700\u9ad8\u6c17\u6e29\t\u7fcc\u65e5\u6700\u9ad8\u6c17\u6e29\n2008-04-01\t\u5317\u6d77\u9053\tNULL\t4.3\t8.4\n2008-04-02\t\u5317\u6d77\u9053\t4.3\t8.4\t9.8\n2008-04-03\t\u5317\u6d77\u9053\t8.4\t9.8\t11.8\n2008-04-04\t\u5317\u6d77\u9053\t9.8\t11.8\t9.8\n2008-04-05\t\u5317\u6d77\u9053\t11.8\t9.8\t14.5<\/pre>\n<h2>\u307e\u3068\u3081<\/h2>\n<p>\u3000n \u884c\u524d\u306e\u884c\u3092\u53d6\u5f97\u3059\u308b\u306b\u306f MAX \u95a2\u6570\u306a\u3069\u306e\u6975\u5024\u95a2\u6570\u3092\u7528\u3044\u308b\u65b9\u6cd5\u3068\uff0cLAG \u95a2\u6570\u3092\u7528\u3044\u308b\u65b9\u6cd5\u3068\u304c\u3042\u308b\uff0en \u884c\u524d\u5f8c\u3092\u6c42\u3081\u308b\u3060\u3051\u306a\u3089\u3044\u305a\u308c\u3067\u3082\u826f\u3044\u304c\uff0c\u6c4e\u7528\u6027\u3092\u8003\u3048\u308b\u3068\u6975\u5024\u95a2\u6570\u3092\u7528\u3044\u3066 ROWS BETWEEN \u53e5\u3092\u7528\u3044\u308b\u65b9\u6cd5\u3092\u899a\u3048\u305f\u307b\u3046\u304c\u826f\u3044\u3060\u308d\u3046\uff0e<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3000SQL Server \u3067\u30a6\u30a3\u30f3\u30c9\u30a6\u95a2\u6570\u3092\u4f7f\u3044\uff0c1\u884c\u524d\u306e\u884c\u3092\u53d6\u5f97\u3059\u308b\u3067\u306f\u30a6\u30a3\u30f3\u30c9\u30a6\u95a2\u6570\u3092\u7528\u3044\u30661\u884c\u524d\u306e\u884c\u3092\u53d6\u5f97\u3057\u305f\uff0e\u4eca\u56de\u306f LAG \u95a2\u6570\u3092\u7528\u3044\u30661\u884c\u524d\u306e\u884c\u3092\u53d6\u5f97\u3059\u308b\uff0e<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[5540,11262,7901,2884,2885,11269,11270,11273,11272,11274,6744,7065,4674,11266,11267,5470,2740,11258,2783,11257,11271,4503,11275],"class_list":{"0":"post-14767","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"hentry","6":"category-technology","7":"tag-between","9":"tag-create-table","10":"tag-date","11":"tag-float","12":"tag-lag","14":"tag-lead","16":"tag-max","17":"tag-not-null","18":"tag-nvarchar","19":"tag-order-by","20":"tag-over","21":"tag-partition-by","22":"tag-rows","23":"tag-sql-server","24":"tag-11258","25":"tag-2783","26":"tag-11257","27":"tag-11271","28":"tag-4503","29":"tag-11275"},"_links":{"self":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/14767","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=14767"}],"version-history":[{"count":7,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/14767\/revisions"}],"predecessor-version":[{"id":14774,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/14767\/revisions\/14774"}],"wp:attachment":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=14767"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=14767"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=14767"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}