GBase 8a\u96c6\u7fa4\u767e\u5206\u4f4d\u51fd\u6570percentile_cont\u548cpercentile_disc\u4ecb\u7ecd<\/a><\/p>\n\n\n\n<\/p>\n\n\n\n
\u539f\u59cb\u6570\u636e\uff0c id3\u662f\u5206\u7ec4\u3002
gbase> select * from testled;
+------+------+------+
| id | id2 | id3 |
+------+------+------+
| 1 | 111 | 1 |
| 2 | 110 | 0 |
| 3 | 109 | 0 |
| 4 | 108 | 1 |
| 5 | 107 | 0 |
| 6 | 106 | 0 |
| 7 | 105 | 1 |
| 8 | 104 | 0 |
+------+------+------+
8 rows in set (Elapsed: 00:00:00.00)<\/p>\n\n\n\n
\u7ed9\u6bcf\u4e00\u5217\uff0c\u6bcf\u4e00\u884c\u505a\u6392\u5e8f\u540e\u7684\u884c\u53f7\u3002
gbase> select
-> id,
-> row_number()over(partition by id3 order by id) num_id,
-> id2,
-> row_number() over(partition by id3 order by id2) num_id2,
-> id3
-> from testled;
+------+--------+------+---------+------+
| id | num_id | id2 | num_id2 | id3 |
+------+--------+------+---------+------+
| 8 | 5 | 104 | 1 | 0 |
| 6 | 4 | 106 | 2 | 0 |
| 5 | 3 | 107 | 3 | 0 |
| 3 | 2 | 109 | 4 | 0 |
| 2 | 1 | 110 | 5 | 0 |
| 7 | 3 | 105 | 1 | 1 |
| 4 | 2 | 108 | 2 | 1 |
| 1 | 1 | 111 | 3 | 1 |
+------+--------+------+---------+------+
8 rows in set (Elapsed: 00:00:00.03)<\/p>\n\n\n\n
\u8ba1\u7b97\u6bcf\u4e00\u4e2a\u5217\u7684\u4e00\u534a\u884c\u53f7
gbase> select *,
-> ceil((max(num_id)over(partition by id3) +1)\/2) half_num_id,
-> ceil((max(num_id2)over(partition by id3)+1)\/2) half_num_id2
-> from (
-> select
-> id,
-> row_number()over(partition by id3 order by id) num_id,
-> id2,
-> row_number() over(partition by id3 order by id2) num_id2,
-> id3
-> from testled
-> )t ;
+------+--------+------+---------+------+-------------+--------------+
| id | num_id | id2 | num_id2 | id3 | half_num_id | half_num_id2 |
+------+--------+------+---------+------+-------------+--------------+
| 6 | 4 | 106 | 2 | 0 | 3 | 3 |
| 5 | 3 | 107 | 3 | 0 | 3 | 3 |
| 3 | 2 | 109 | 4 | 0 | 3 | 3 |
| 2 | 1 | 110 | 5 | 0 | 3 | 3 |
| 8 | 5 | 104 | 1 | 0 | 3 | 3 |
| 4 | 2 | 108 | 2 | 1 | 2 | 2 |
| 1 | 1 | 111 | 3 | 1 | 2 | 2 |
| 7 | 3 | 105 | 1 | 1 | 2 | 2 |
+------+--------+------+---------+------+-------------+--------------+
8 rows in set (Elapsed: 00:00:00.31)<\/p>\n\n\n\n
\u5b8c\u6574\u8ba1\u7b97\u3002\u6bcf\u4e2a\u5217\u7684\u4e2d\u6570<\/p>\n
gbase> select\n -> max(case when num_id=half_num_id then id else null end) over(partition by id3) id,\n -> max(case when num_id2=half_num_id2 then id2 else null end) over (partition by id3) id2,\n -> id3\n -> from (\n -> select *,\n -> ceil((max(num_id)over(partition by id3) +1)\/2) half_num_id, \n -> ceil((max(num_id2)over(partition by id3)+1)\/2) half_num_id2 \n -> from (\n -> select\n -> id,\n -> row_number()over(partition by id3 order by id) num_id,\n -> id2,\n -> row_number() over(partition by id3 order by id2) num_id2,\n -> id3\n -> from testled\n -> )t\n -> )tt where num_id=half_num_id or num_id2=half_num_id2;\n+------+------+------+\n| id | id2 | id3 |\n+------+------+------+\n| 5 | 107 | 0 |\n| 4 | 108 | 1 |\n+------+------+------+\n2 rows in set (Elapsed: 00:00:00.03)\n<\/pre><\/p>\n\n\n\n\u4e24\u4e2a\u5217\u7684<\/p>\n\n\n\n
select \n max(case when num_id=half_num_id then id else null end) over(partition by id3) id, \n max(case when num_id2=half_num_id2 then id2 else null end) over (partition by id3) id2, \n id3\n from (\n select *,\n ceil((max(num_id)over(partition by id3) +1)\/2) half_num_id, \n ceil((max(num_id2)over(partition by id3)+1)\/2) half_num_id2 \n from (\n select \n id,\n row_number()over(partition by id3 order by id) num_id,\n id2,\n row_number() over(partition by id3 order by id2) num_id2,\n id3 \n from testled\n )t \n)tt where num_id=half_num_id or num_id2=half_num_id2;\n<\/pre>","protected":false},"excerpt":{"rendered":"\u5357\u5927\u901a\u7528GBase 8a\u96c6\u7fa4\u4e2d\u6570\u3001\u4e2d\u4f4d\u6570\u7684\u5b9e\u73b0<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-250","post","type-post","status-publish","format-standard","hentry","category-gbase8a"],"_links":{"self":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/250","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/comments?post=250"}],"version-history":[{"count":12,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/250\/revisions"}],"predecessor-version":[{"id":12963,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/250\/revisions\/12963"}],"wp:attachment":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/media?parent=250"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/categories?post=250"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/tags?post=250"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}