CodePudding user response:
; With cte as (
The select evenid netstation, count (1) as the from CNT t group by evenid, netstation
)
The r1 as (
The select evenid, netstation
, avg (ml) as avg_v
, avg (mb1) as avg_mb1
, avg (mb2) as avg_mb2
, avg (msBB) as avg_msBB
The from t as a inner join cte as b on a.e venid=b.e venid and a.netstation=b.net station
Where biggest nt<5
Group by evenid, netstation
)
, t2 as (
Select * from (
Select row_number () over (partition evenid, netstation order by ml + mb1 + mb2 + msBB desc) as rids, a. *, the biggest nt from t as a inner join cte as b
On a.e venid=b.e venid and a.netstation=b.net station
Where biggest nt>=5
) as tt
Where rid> 1 the and rids! CNT=
)
R2 as (
The select evenid, netstation
, avg (ml) as avg_v
, avg (mb1) as avg_mb1
, avg (mb2) as avg_mb2
, avg (msBB) as avg_msBB
The from t2
Group by evenid, netstation
)
Select * from r1
Union all
Select * from r2
CodePudding user response:
No test data, so there is no verification,You see, in fact, it is not hard to, understand it, according to the idea change can run,
CodePudding user response:
Computer suddenly goes down, posted the test data, the repairSET NAMES utf8mb4;
The SET FOREIGN_KEY_CHECKS=0;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Table structure for earthquake_stat
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
DROP TABLE IF the EXISTS ` earthquake_stat `;
The CREATE TABLE ` earthquake_stat ` (
` id ` int (11), NOT NULL AUTO_INCREMENT COMMENT 'on the id, as the table's primary key',
` eventid ` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'earthquake event ID, as the event table's foreign key',
` netstation ` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'network of stations,
` short ` double (11, 2) NOT NULL DEFAULT - 1.00 - the COMMENT 'epicentral distance degree,
` ml ` double (11, 8) NOT NULL DEFAULT - 1.00000000,
` mb1 ` double (11, 8) NOT NULL DEFAULT - 1.00000000 - the COMMENT 'refers to the magnitude of MB,
` mb2 ` double (11, 8) NOT NULL DEFAULT - 1.00000000 - the COMMENT 'refers to the magnitude of mB,
` msBB ` double (11, 8) NOT NULL DEFAULT - 1.00000000,
` pTime ` datetime (0) NOT NULL COMMENT 'P wave by that time,
PRIMARY KEY (` id `) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12920 CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Records of earthquake_stat
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
INSERT INTO ` earthquake_stat ` VALUES (1, '20191217111750', 'QH. LEH. 00. BHZ, 46.31, 1.00000000, 5.55438771, 6.70477852, 6.03926671,' the 2019-12-17 19:10:31 ');
INSERT INTO ` earthquake_stat ` VALUES (2, '20191217111750', '00 XZ) ZHF). BHZ, 50.70, 1.00000000, 5.99896267, 5.70109382, 5.11351366,' the 2019-12-17 19:11:29 ');
INSERT INTO ` earthquake_stat ` VALUES (3, '20191217120814', 'XJ. WNQ. 00. BHZ, 1.48, 3.84471630, 1.00000000, 1.00000000, 4.22272603,' the 2019-12-17 20:06:40 ');
INSERT INTO ` earthquake_stat ` VALUES (4, '20191217120814', 'XJ. LSG. 00. BHZ, 2.52, 4.38947441, 1.00000000, 1.00000000, 4.50544545,' the 2019-12-17 20:06:56 ');
INSERT INTO ` earthquake_stat ` VALUES (5, '20191217120814', 'XJ. XNY. 00. BHZ, 3.38, 4.34511777, 1.00000000, 1.00000000, 4.22714363,' the 2019-12-17 20:07:06 ');
INSERT INTO ` earthquake_stat ` VALUES (6, '20191217120814', 'XJ. Captured. 00. BHZ, 3.83, 4.34804329, 1.00000000, 1.00000000, 4.11841309,' the 2019-12-17 20:07:17 ');
INSERT INTO ` earthquake_stat ` VALUES (7, '20191217120814', 'XJ. FUH. 00. BHZ, 4.38, 3.49249629, 1.00000000, 1.00000000, 3.48971106,' the 2019-12-17 20:07:26 ');
INSERT INTO ` earthquake_stat ` VALUES (8, '20191217120814', 'XJ. KNS. 00. BHZ, 4.98, 3.45560554, 1.00000000, 1.00000000, 3.44423112,' the 2019-12-17 20:07:27 ');
INSERT INTO ` earthquake_stat ` VALUES (9, '20191217120814', 'XJ. LTA. 00. BHZ, 5.05, 4.25690109, 5.20812244, 5.35481069, 4.00039789,' the 2019-12-17 20:07:20 ');
INSERT INTO ` earthquake_stat ` VALUES (10, '20191217120814', 'XJ. AKS. 00. BHZ, 5.39, 3.62614248, 4.89469273, 4.88790930, 3.55002247,' the 2019-12-17 20:07:36 ');
INSERT INTO ` earthquake_stat ` VALUES (11, '20191217120814', 'XJ. BLT. 00. BHZ, 5.44, 3.81304103, 4.94677373, 4.83794639, 3.63880494,' the 2019-12-17 20:07:37 ');
INSERT INTO ` earthquake_stat ` VALUES (12 '20191217121045', 'XJ. WNQ. 00. BHZ, 5.11, 4.99469901, 6.79657393, 6.52761268, 5.11689452,' the 2019-12-17 20:06:40 ');
INSERT INTO ` earthquake_stat ` VALUES (13, '20191217121045', 'XJ. LSG. 00. BHZ, 7.10, 5.38947077, 7.38961381, 6.42378772, 5.25150119,' the 2019-12-17 20:06:56 ');
INSERT INTO ` earthquake_stat ` VALUES (14, '20191217121045', 'XJ. XNY. 00. BHZ, 7.27, 4.99532861, 5.46608454, 6.08594295, 4.77995372,' the 2019-12-17 20:07:06 ');
INSERT INTO ` earthquake_stat ` VALUES (15, '20191217121045', 'XJ. LTA. 00. BHZ, 8.69, 5.05001330, 5.45508388, 6.01400755, 4.77459540,' the 2019-12-17 20:07:20 ');
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull