Count the maximum and minimum records in each group

Mongo implementation

{
     "_id" : "01001",
     "city" : "AGAWAM",
     "pop" : 15338,
     "state" : "MA"
}

For complete JSON, see:http://media.mongodb.org/zips…

demand

Get the city with the largest population and the city with the smallest population in each state and the corresponding population

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"}
    }}
)

effect

{ "_id" : "DE", "biggestCity" : "NEWARK", "biggestPop" : 111674, "smallestCity" : "BETHEL", "smallestPop" : 108 }
{ "_id" : "MS", "biggestCity" : "JACKSON", "biggestPop" : 204788, "smallestCity" : "CHUNKY", "smallestPop" : 79 }
...

See:https://docs.mongodb.com/manu…

MySQL implementation

Compared with Mongo, it’s much more intuitive

Scheme I

#You need to set a larger value. The default 1024 is not enough
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 ;

reference resources

https://dev.mysql.com/doc/ref…
https://dev.mysql.com/doc/ref…

Scheme II

#Each state group is sorted in ascending and descending order according to pop, and a sequence number is manually assigned. Take the sequence number one to get the start and end records of the group

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

supplement

Create table statement

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/\[\(.*\)\]$//' | awk -F, '{print "insert into zipcode select null," $1"," $2","$3","$4";"}'