資訊專欄INFORMATION COLUMN
摘要：統(tǒng)計(jì)每個(gè)分組里的最大最小記錄實(shí)現(xiàn)完整見(jiàn)需求得到每個(gè)擁有最多人口的城市和擁有最小人口的城市以及對(duì)應(yīng)的人口數(shù)效果見(jiàn)實(shí)現(xiàn)相比的直觀就要繞很多了方案一需要設(shè)置一個(gè)較大值默認(rèn)的還不夠用參考方案二每個(gè)分組里面分別按升序降序排序人為分配一個(gè)序號(hào)均取序號(hào)
統(tǒng)計(jì)每個(gè)分組里的最大最小記錄 mongo實(shí)現(xiàn)
{
"_id" : "01001",
"city" : "AGAWAM",
"pop" : 15338,
"state" : "MA"
}
完整json見(jiàn)： http://media.mongodb.org/zips...
需求得到每個(gè)state擁有最多人口的城市和擁有最小人口的城市以及對(duì)應(yīng)的人口數(shù)
db.zipcodes.aggregate(
{$group: {_id:{state:"$state",city:"$city"}, popPerCity:{$sum:"$pop"} } },
{$sort: {popPerCity:1} },
{$group: {
_id:"$_id.state",
biggestCity:{$last:"$_id.city"},
biggestPop: {$last:"$popPerCity"},
smallestCity: {$first:"$_id.city"},
smallestPop: {$first:"$popPerCity"}
}}
)
效果
{ "_id" : "DE", "biggestCity" : "NEWARK", "biggestPop" : 111674, "smallestCity" : "BETHEL", "smallestPop" : 108 }
{ "_id" : "MS", "biggestCity" : "JACKSON", "biggestPop" : 204788, "smallestCity" : "CHUNKY", "smallestPop" : 79 }
...
見(jiàn)： https://docs.mongodb.com/manu...
Mysql實(shí)現(xiàn)相比mongo的直觀 就要繞很多了
方案一# 需要設(shè)置一個(gè)較大值 默認(rèn)的1024還不夠用 SET SESSION group_concat_max_len = 20480; select state, substring_index(group_concat(city order by pop ),",",1) smallestCity, min(pop),substring_index(group_concat(city order by pop ),",",-1) biggestCity, max(pop) from (select state, city, sum(pop) pop from zipcode group by state, city) a group by state ;
參考
https://dev.mysql.com/doc/ref...
https://dev.mysql.com/doc/ref...
# 每個(gè)state分組里面分別按pop升序、降序排序 人為分配一個(gè)序號(hào) 均取序號(hào)一就得到了該分組的起止記錄 select b.state, b.city smallestCity, b.pop smallestPop, c.city biggestCity, c.pop biggestPop from ( select state,city,pop,@rank:=if(@current_state=state, @rank+1, 1) rank, @current_state:=state from (select state, city, sum(pop) pop from zipcode group by state, city) a, (select @current_state:=NULL, @rank:=NULL) vars order by a.state,a.pop ) b , ( select state,city,pop,@rank:=if(@current_state=state, @rank+1, 1) rank, @current_state:=state from (select state, city, sum(pop) pop from zipcode group by state, city) a, (select @current_state:=NULL, @rank:=NULL) vars order by a.state,a.pop desc ) c where b.state = c.state and b.rank = 1 and c.rank = 1補(bǔ)充
建表語(yǔ)句
CREATE TABLE `zipcode` ( `id` int(11) NOT NULL AUTO_INCREMENT, `zipcode` varchar(10) NOT NULL, `city` varchar(30) NOT NULL, `pop` int(11) NOT NULL, `state` varchar(5) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `zipcode` (`zipcode`), KEY `idx_state_city` (`state`,`city`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
json ==> batch insert sql
jq -c "[._id, .city, .pop, .state]" zips.json | sed "s/[(.*)]$/1/" | awk -F, "{print "insert into zipcode select null," $1"," $2","$3","$4";"}"
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/19020.html
摘要：在第行中，我們會(huì)從集合取得結(jié)果并顯示它。的邏輯在中，我們要以性別作為，然后以作為。年齡是用來(lái)做計(jì)算用的，而名字只是用來(lái)顯示給人看的。我們要檢查所有和性別相關(guān)的年齡，找到年齡最大和最小的用戶。 在這篇文章里面，我們會(huì)演示如何在 MongoDB 中使用 MapReduce 操作。我們會(huì)用 dummy-json 這個(gè)包來(lái)生成一些虛假的數(shù)據(jù)，然后用 Mongojs 如果想要快速看到結(jié)果，可以到...
摘要：先進(jìn)行過(guò)濾，再分組實(shí)例解釋進(jìn)行過(guò)濾，這里利用兩個(gè)字段進(jìn)行過(guò)濾。聚合操作可以對(duì)分組的數(shù)據(jù)執(zhí)行如下的表達(dá)式計(jì)算計(jì)算總和。根據(jù)分組，獲取集合中所有文檔對(duì)應(yīng)值得最大值。將指定的表達(dá)式的值添加到一個(gè)數(shù)組中。 先進(jìn)行過(guò)濾，再分組 1、實(shí)例： db.getCollection(UpMsgItem).aggregate( [ {$match : { createTime : {$gt : ...
摘要：之所以把計(jì)數(shù)排序桶排序基數(shù)排序放在一起比較，是因?yàn)樗鼈兊钠骄鶗r(shí)間復(fù)雜度都為。動(dòng)畫(huà)計(jì)數(shù)排序思想找出待排序的數(shù)組中最大和最小的元素。桶排序計(jì)數(shù)排序能派上用場(chǎng)嗎手機(jī)號(hào)碼有位，范圍太大，顯然不適合用這兩種排序算法。 showImg(https://segmentfault.com/img/bVbuF9e?w=900&h=500); 1. 前言 算法為王。 想學(xué)好前端，先練好內(nèi)功，只有內(nèi)功深厚者...
閱讀 3372·2021-11-04 16:10
閱讀 3871·2021-09-29 09:43
閱讀 2708·2021-09-24 10:24
閱讀 3371·2021-09-01 10:46
閱讀 2519·2019-08-30 15:54
閱讀 602·2019-08-30 13:19
閱讀 3245·2019-08-29 17:19
閱讀 1067·2019-08-29 16:40
