{"id":1435,"date":"2021-04-26T10:25:55","date_gmt":"2021-04-26T01:25:55","guid":{"rendered":"https:\/\/www.kwonline.org\/memo2\/?p=1435"},"modified":"2022-12-09T13:12:17","modified_gmt":"2022-12-09T04:12:17","slug":"fizzbuzz-for-sql","status":"publish","type":"post","link":"https:\/\/www.kwonline.org\/memo2\/2021\/04\/26\/fizzbuzz-for-sql\/","title":{"rendered":"SQL \u3067 FizzBuzz"},"content":{"rendered":"

 
\nPostgreSQL \u3067 FizzBuzz.<\/p>\n

\u9762\u63a5\u306e\u969b\u306b\u3069\u3046\u305e\u3002(\u8b0e<\/p>\n

\r\nwith recursive fizzbuzz(i, result) as (\r\n  select\r\n  1, '1'\r\n  union all\r\n  select\r\n  i + 1\r\n  ,case when (i + 1) % 15 = 0 then 'FizzBuzz'\r\n        when (i + 1) % 5 = 0 then 'Buzz'\r\n        when (i + 1) % 3=  0 then 'Buzz'\r\n        else (i + 1)::text end\r\n  from fizzbuzz\r\n  where i < 100\r\n)\r\nselect * from fizzbuzz;\r\n<\/pre>\n
 
\nBigQuery\u7528\u306b loop \u3067\u66f8\u304d\u76f4\u305d\u3046\u3068\u601d\u3063\u305f\u3051\u3069\u30e1\u30f3\u30c9\u30af\u30b5\u30a4\u304b\u3089\u3084\u3081\u305f\uff57
\n\u624b\u8efd\u306b\u3084\u308b\u306a\u3089\u3053\u308c\u304b\u3002<\/p>\n
\r\nselect\r\ni,\r\ncase when mod(i, 15) = 0 then 'FizzBuzz'\r\n     when mod(i, 5) = 0 then 'Buzz'\r\n     when mod(i, 3) = 0 then 'Fizz'\r\n     else cast(i as string) end as result\r\nfrom unnest(generate_array(1, 100)) as i;\r\n<\/pre>\n
\u203b\u3044\u3064\u306e\u9593\u306b\u304b BigQuery \u306b\u3082 Recursive \u5b9f\u88c5\u3055\u308c\u3066\u305f\u3002
\nhttps:\/\/cloud.google.com\/bigquery\/docs\/reference\/standard-sql\/query-syntax#with_clause<\/a><\/p>\n
 
\nMS SQL Server \u306a\u3089 T-SQL \u3067\u3053\u3046\u304b\u3002
\n\u4e00\u6642\u30c6\u30fc\u30d6\u30eb\u4f7f\u3046\u3068\u30e9\u30af\u306d\u3002<\/p>\n
\r\ndeclare @i int = 1;\r\ndrop table if exists #temptbl;\r\ncreate table #temptbl(i int, result varchar(8));\r\nwhile @i <= 100\r\nbegin\r\n\tinsert into #temptbl\r\n\tselect\r\n\t\t@i as i,\r\n\t\tcase \r\n\t\t\twhen @i % 15 = 0 then 'FizzBuzz'\r\n\t\t\twhen @i % 5 = 0 then 'Buzz'\r\n\t\t\twhen @i% 3 = 0 then 'Fizz'\r\n\t\t\telse convert(varchar(8), @i) end as result\r\n\tset @i = @i + 1\r\nend\r\nselect * from #temptbl;\r\n<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
  PostgreSQL \u3067 FizzBuzz. \u9762\u63a5\u306e\u969b\u306b\u3069\u3046\u305e\u3002(\u8b0e with recursive fizzbuzz(i, result) as ( select 1, ‘1’ union all sele […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21,20],"tags":[],"class_list":["post-1435","post","type-post","status-publish","format-standard","hentry","category-data-engineering","category-sql"],"_links":{"self":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts\/1435","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/comments?post=1435"}],"version-history":[{"count":11,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts\/1435\/revisions"}],"predecessor-version":[{"id":1876,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts\/1435\/revisions\/1876"}],"wp:attachment":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/media?parent=1435"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/categories?post=1435"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/tags?post=1435"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}