{"id":468,"date":"2023-02-20T21:18:06","date_gmt":"2023-02-20T13:18:06","guid":{"rendered":"http:\/\/blog.yuekegu.com\/?p=468"},"modified":"2023-02-20T21:18:06","modified_gmt":"2023-02-20T13:18:06","slug":"mysql%e8%ae%a1%e7%ae%97%e6%ad%a3%e6%80%81%e5%88%86%e5%b8%83%e3%80%81%e5%b7%ae%e5%bc%82%e7%b3%bb%e6%95%b0%e3%80%81%e7%a6%bb%e5%9d%87%e5%b7%ae%e3%80%81%e7%a6%bb%e5%9d%87%e5%b7%ae%e7%8e%87","status":"publish","type":"post","link":"https:\/\/blog.yuekegu.com\/?p=468","title":{"rendered":"mysql\u8ba1\u7b97\u6b63\u6001\u5206\u5e03\u3001\u5dee\u5f02\u7cfb\u6570\u3001\u79bb\u5747\u5dee\u3001\u79bb\u5747\u5dee\u7387"},"content":{"rendered":"\n

1\u3001\u6570\u636e\u51c6\u5907<\/strong>\u3000\u3000\u5efa\u8868\u3000<\/p>\n\n\n\n

drop table if exists score;\ncreate table score(\n    id int not null primary key auto_increment,\n    score decimal(10<\/strong>,2<\/strong>) not null\n);<\/pre>\n\n\n\n
\u3000\u3000\u63d2\u5165\u6570\u636e<\/p>\n\n\n\n
insert into score (score) values (89.4<\/strong>);\ninsert into score (score) values (82.5<\/strong>);\ninsert into score (score) values (65<\/strong>);\ninsert into score (score) values (98<\/strong>);\ninsert into score (score) values (92<\/strong>);\ninsert into score (score) values (34<\/strong>);\ninsert into score (score) values (59<\/strong>);\ninsert into score (score) values (83<\/strong>);\ninsert into score (score) values (88<\/strong>);\ninsert into score (score) values (63<\/strong>);\ninsert into score (score) values (100<\/strong>);\ninsert into score (score) values (96<\/strong>);\ninsert into score (score) values (68<\/strong>);\ninsert into score (score) values (56<\/strong>);<\/pre>\n\n\n\n
\u6ce8\u610f\uff1a\u8ba1\u7b97\u6807\u51c6\u5dee\u6700\u5c11\u9700\u8981\u4e24\u4e2a\u503c<\/p>\n\n\n\n
2\u3001\u8ba1\u7b97\u6b63\u6001\u5206\u5e03<\/strong><\/p>\n\n\n\n
\u8ba1\u7b97\u65b9\u6cd5\u662f\uff1a\u5e73\u5747\u5206-\u6807\u51c6\u5dee\uff08\u56db\u4e2a\u503c\uff1a\u03bc-2\u03c3\uff0c\u03bc-\u03c3\uff0c\u03bc+\u03c3\uff0c\u03bc+2\u03c3\uff09<\/p>\n\n\n\n
select round(score_avg - score_bzc * 2<\/strong>, 2<\/strong>) zt1,\n       round(score_avg - score_bzc, 2<\/strong>)     zt2,\n       round(score_avg + score_bzc, 2<\/strong>)     zt3,\n       round(score_avg + score_bzc * 2<\/strong>, 2<\/strong>) zt4\nfrom (select avg(score) score_avg, stddev_samp(score) score_bzc\n      from score\n      where id in (1<\/strong>, 2<\/strong>)) as a;<\/pre>\n\n\n\n
3\u3001\u8ba1\u7b97\u5dee\u5f02\u7cfb\u6570<\/strong><\/p>\n\n\n\n
 \u8ba1\u7b97\u65b9\u6cd5\u662f\uff1a\u6807\u51c6\u5dee\/\u5e73\u5747\u5206\uff08\u03c3\/\u03bc\uff09<\/p>\n\n\n\n
select round(score_bzc\/score_avg,2<\/strong>) cyxs\nfrom (select avg(score) score_avg, stddev_samp(score) score_bzc\n      from score\n      where id in (1<\/strong>,2<\/strong>)) as a;<\/pre>\n\n\n\n
4\u3001\u8ba1\u7b97\u79bb\u5747\u5dee<\/strong><\/p>\n\n\n\n
\u8ba1\u7b97\u65b9\u6cd5\u662f\uff1a\u5355\u4e2a\u503c-\u5e73\u5747\u503c\uff08x-\u03bc\uff09<\/p>\n\n\n\n
select round(score - (avg(score) over ()), 2<\/strong>) ljc\nfrom score\nwhere id in (1<\/strong>, 2<\/strong>);<\/pre>\n\n\n\n
5\u3001\u8ba1\u7b97\u79bb\u5747\u5dee\u7387<\/strong><\/p>\n\n\n\n
\u8ba1\u7b97\u65b9\u6cd5\u662f\uff1a\u79bb\u5747\u5dee\/\u5e73\u5747\u503c\uff08\uff08x-\u03bc\uff09\/\u03bc\uff09<\/p>\n\n\n\n
select round((score - (avg(score) over ())) \/ (avg(score) over ()), 2<\/strong>) ljcl\nfrom score\nwhere id in (1<\/strong>, 2<\/strong>);<\/pre>\n","raw":"","protected":false},"excerpt":{"rendered":"
1\u3001\u6570\u636e\u51c6\u5907\u3000\u3000\u5efa\u8868\u3000 drop table if exists score; create table sc … <\/p>\n
\u7ee7\u7eed\u9605\u8bfb\u201cmysql\u8ba1\u7b97\u6b63\u6001\u5206\u5e03\u3001\u5dee\u5f02\u7cfb\u6570\u3001\u79bb\u5747\u5dee\u3001\u79bb\u5747\u5dee\u7387\u201d<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"post_thumbnail_image":"","content_first_image":"","post_medium_image_300":"","post_thumbnail_image_624":"","post_frist_image":"","post_medium_image":"","post_large_image":"","post_full_image":"","post_all_images":[],"videoAdId":"","listAd":"0","listAdId":"","listAdEvery":5,"total_comments":0,"category_name":"\u5fc3\u7406\u5b66","post_date":"2023-02-20","like_count":"0","praiseWord":"\u9f13\u52b1","copyright_state":"","excitationAd":"0","rewardedVideoAdId":"","detailAdId":"","detailAd":"0","enterpriseMinapp":"0","audios":[],"postImageUrl":false,"avatarurls":[],"related_posts":null,"pageviews":789,"next_post_id":506,"next_post_title":"pycharm\u6fc0\u6d3b","previous_post_id":466,"previous_post_title":"\u6b63\u5219\u63d0\u53d6\u8981\u9009\u62e9\u9898\u7684\u9009\u9879","_links":{"self":[{"href":"https:\/\/blog.yuekegu.com\/index.php?rest_route=\/wp\/v2\/posts\/468"}],"collection":[{"href":"https:\/\/blog.yuekegu.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.yuekegu.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.yuekegu.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.yuekegu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=468"}],"version-history":[{"count":1,"href":"https:\/\/blog.yuekegu.com\/index.php?rest_route=\/wp\/v2\/posts\/468\/revisions"}],"predecessor-version":[{"id":469,"href":"https:\/\/blog.yuekegu.com\/index.php?rest_route=\/wp\/v2\/posts\/468\/revisions\/469"}],"wp:attachment":[{"href":"https:\/\/blog.yuekegu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.yuekegu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=468"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.yuekegu.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}