{"id":1883,"date":"2021-10-16T07:20:02","date_gmt":"2021-10-15T22:20:02","guid":{"rendered":"https:\/\/suzutukiblog.com\/?p=1883"},"modified":"2022-10-08T00:31:06","modified_gmt":"2022-10-07T15:31:06","slug":"mysql19","status":"publish","type":"post","link":"https:\/\/suzutukiblog.com\/index.php\/2021\/10\/16\/mysql19\/","title":{"rendered":"MySQL(\u305d\u306e19 \u5e73\u5747\u5ba2\u5358\u4fa1\u306a\u3069\u5fdc\u7528)"},"content":{"rendered":"

\u305d\u306e18\u304b\u3089\u7d9a\u304d\u307e\u3059\u3002<\/a><\/p>\n

\u5fdc\u75281)\u5168\u671f\u9593\u306b\u304a\u3051\u308b\u5e73\u5747\u5ba2\u5358\u4fa1\u3092\u77e5\u308a\u305f\u3044\uff01<\/h3>\n

\u88dc\u8db3:\u5358\u4fa1\u306f\u5c0f\u6570\u7b2c1\u4f4d\u3067\u56db\u6368\u4e94\u5165
\n\u5ba2\u5358\u4fa1<\/strong>\u3068\u306f1\u56de\u306e\u8cfc\u8cb7\u306b\u3088\u3063\u3066\u30e6\u30fc\u30b6\u30fc1\u4eba\u5f53\u305f\u308a\u304c\u652f\u6255\u3046\u7dcf\u984d\u306e\u3053\u3068\u3002
\n\u4f8b\u3048\u3070\u30e6\u30fc\u30b6\u30fcA\u3055\u3093\u304c\u30ab\u30d5\u30a7\u3067\u98f2\u98df\u3092\u3057\u305f\u3068\u304d
\n\u30b3\u30fc\u30d2\u30fc300\u5186\u3068\u30b1\u30fc\u30ad400\u5186\u3092\u8cfc\u5165\u3057\u305f\u3068\u304d\u3001\u3053\u306e\u5834\u5408\u5ba2\u5358\u4fa1\u306f700\u5186<\/p>\n

select round(avg(amount), 0) from orders o;<\/strong><\/pre>\n
\"\"<\/p>\n
\u5fdc\u75282)\u6708\u5225\u306e\u5e73\u5747\u5ba2\u5358\u4fa1\u3092\u8abf\u3079\u305f\u3044\uff01(\u5c0f\u6570\u7b2c1\u4f4d\u3067\u56db\u6368\u4e94\u5165)<\/h3>\n
<\/p>\n
select\r\ndate_format(order_time,'%Y%m') as order_year_month,\r\nround(avg(amount),0) as average_customer_spend\r\nfrom\r\norders\r\ngroup by\r\ndate_format(order_time,'%Y%m')\r\norder by order_year_month;<\/strong><\/pre>\n
\"\"<\/p>\n
\u203b\u30dd\u30a4\u30f3\u30c8<\/h3>\n
group by\u53e5<\/strong>\u3092\u4f7f\u3063\u3066\u3001\u5e74\u6708\u3054\u3068\u3067\u96c6\u7d04\u3057\u3066\u5e74\u6708\u5225\u306b\u5e73\u5747\u5ba2\u5358\u4fa1\u3092\u6c42\u3081\u308b\u3002<\/p>\n
amount.order_time\u306b\u306f\u3001datetime\u578b\u3067\u8cfc\u5165\u65e5\u6642\u304c\u5165\u3063\u3066\u3044\u308b\u306e\u3067<\/p>\n
“201701”\u3068\u3044\u3063\u305f\u3001\u65e5\u4ed8\u3051\u3092\u6307\u5b9a\u3055\u308c\u305f\u66f8\u5f0f\u306b\u8a2d\u5b9a\u3059\u308b\u306b\u306f<\/p>\n
date_format(order_time,’%Y%m’)\u3068\u3059\u308b\u3053\u3068\u3067\u3067\u304d\u308b\u3002<\/p>\n
\u5fdc\u75283)\u90fd\u9053\u5e9c\u770c\u5225\u5e73\u5747\u5ba2\u5358\u4fa1\u3092\u8abf\u3079\u305f\u3044\uff01<\/h3>\n
\u88dc\u8db3\uff1a\u5fc5\u8981\u306a\u5217\u306f\u3001\u90fd\u9053\u5e9c\u770cID\u3001\u90fd\u9053\u5e9c\u770c\u540d
\n\u5e73\u5747\u5ba2\u5358\u4fa1\u5c0f\u6570\u7b2c1\u4f4d\u3067\u56db\u6368\u4e94\u5165 \u4e26\u3073\u9806\u306f\u90fd\u9053\u5e9c\u770cID\u6607\u9806<\/p>\n
select\r\np.id,p.name,round(avg(amount),0) as average_customer_spend\r\nfrom\r\norders o\r\ninner join\r\nusers u\r\non o.user_id = u.id\r\ninner join\r\nprefectures p\r\non u.prefecture_id = p.id\r\ngroup by\r\np.id\r\norder by\r\np.id<\/strong><\/pre>\n
\"\"<\/p>\n
\u203b\u30dd\u30a4\u30f3\u30c8<\/h3>\n
group by\u53e5<\/strong>\u3067\u90fd\u9053\u5e9c\u770cID\u3092\u96c6\u7d04\u3057\u3066\u3001round(avg(amount),0)<\/strong>\u3067\u5e73\u5747\u5ba2\u5358\u4fa1\u3092\u6c42\u3081\u308b\u3002inner join<\/strong>\u3067\u90fd\u9053\u5e9c\u770cID\u540c\u58eb\u3067\u7d50\u5408\u3055\u305b\u308b\u3002<\/p>\n
\u5fdc\u75284)\u90fd\u9053\u5e9c\u770c\u5225\u30fb\u6708\u5225\u5e73\u5747\u5ba2\u5358\u4fa1\u3092\u77e5\u308a\u305f\u3044\uff01<\/h3>\n
\u5fc5\u8981\u306a\u5217\u306f\u90fd\u9053\u5e9c\u770cID,\u90fd\u9053\u5e9c\u770c\u540d,\u5e74\u6708,\u5e73\u5747\u5ba2\u5358\u4fa1(\u5c0f\u6570\u7b2c1\u4f4d\u3067\u56db\u6368\u4e94\u5165)<\/strong>
\n\u4e26\u3073\u9806\u306f\u90fd\u9053\u5e9c\u770cid\u6607\u9806\u3001<\/strong>\u6b21\u306b\u5e74\u6708\u6607\u9806<\/strong>
\n\u30d2\u30f3\u30c8:group by\u53e5<\/strong>\u306b\u306f\u8907\u6570\u306e\u5217\u3092\u6307\u5b9a\u3067\u304d\u308b\u3002
\n\u4f8b\u3048\u3070 group by prefecture_id, order_year_month<\/strong><\/p>\n
select\r\np.id, p.name, date_format(o.order_time,'%Y%m') as order_year_month,\r\nround(avg(amount),0) as average_customer_spend\r\nfrom\r\norders o\r\ninner join\r\nusers u\r\non o.user_id = u.id\r\ninner join\r\nprefectures p\r\non u.prefecture_id = p.id\r\ngroup by\r\np.id, date_format(o.order_time,'%Y%m')\r\norder by\r\np.id, order_year_month;<\/strong><\/pre>\n
\"\"<\/p>\n
\u203b\u30dd\u30a4\u30f3\u30c8<\/h3>\n
order by\u53e5<\/strong>\u3082\u8907\u6570\u6307\u5b9a\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u308b\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u305d\u306e18\u304b\u3089\u7d9a\u304d\u307e\u3059\u3002 \u5fdc\u75281)\u5168\u671f\u9593\u306b\u304a\u3051\u308b\u5e73\u5747\u5ba2\u5358\u4fa1\u3092\u77e5\u308a\u305f\u3044\uff01 \u88dc\u8db3:\u5358\u4fa1\u306f\u5c0f\u6570\u7b2c1\u4f4d\u3067\u56db\u6368\u4e94\u5165 \u5ba2\u5358\u4fa1\u3068\u306f1\u56de\u306e\u8cfc\u8cb7\u306b\u3088\u3063\u3066\u30e6\u30fc\u30b6\u30fc1\u4eba\u5f53\u305f\u308a\u304c\u652f\u6255\u3046\u7dcf\u984d\u306e\u3053\u3068\u3002 \u4f8b\u3048\u3070\u30e6\u30fc\u30b6\u30fcA\u3055\u3093\u304c\u30ab\u30d5\u30a7\u3067\u98f2\u98df\u3092\u3057\u305f\u3068\u304d \u30b3 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"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,"footnotes":""},"categories":[5,27],"tags":[],"class_list":["post-1883","post","type-post","status-publish","format-standard","hentry","category-mysql","category-programming-note"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/suzutukiblog.com\/index.php\/wp-json\/wp\/v2\/posts\/1883","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/suzutukiblog.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/suzutukiblog.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/suzutukiblog.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/suzutukiblog.com\/index.php\/wp-json\/wp\/v2\/comments?post=1883"}],"version-history":[{"count":8,"href":"https:\/\/suzutukiblog.com\/index.php\/wp-json\/wp\/v2\/posts\/1883\/revisions"}],"predecessor-version":[{"id":6033,"href":"https:\/\/suzutukiblog.com\/index.php\/wp-json\/wp\/v2\/posts\/1883\/revisions\/6033"}],"wp:attachment":[{"href":"https:\/\/suzutukiblog.com\/index.php\/wp-json\/wp\/v2\/media?parent=1883"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/suzutukiblog.com\/index.php\/wp-json\/wp\/v2\/categories?post=1883"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/suzutukiblog.com\/index.php\/wp-json\/wp\/v2\/tags?post=1883"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}