SQL查询需求：按年月统计注册用户数及对应订单总金额

现有users和bookings两张数据表，需完成以下查询：

• 按指定年份的各月份统计注册用户数量；
• 统计对应年份、月份及用户所属国家的订单总金额。

已通过单表查询正确获取用户统计结果，但关联bookings表后无法得到正确的金额数据，寻求正确的关联查询方案。

表结构与测试数据 #

users表创建语句 #

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `user_nationality` varchar(255) DEFAULT NULL,
  `user_birthYear` int(11) DEFAULT NULL,
  `user_email` varchar(255) DEFAULT NULL,
  `user_passportNumber` varchar(255) DEFAULT NULL,
  `user_hotel` varchar(255) DEFAULT NULL,
  `gender` varchar(255) NOT NULL,
  `addedOn` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

users表测试数据 #

INSERT INTO `users` (`user_id`, `user_name`, `user_nationality`, `user_birthYear`, `user_email`, `user_passportNumber`, `user_hotel`, `gender`, `addedOn`) VALUES
(104, 'john abraham', 'albania', 1994, 'john@john.com', '11100', 'google', 'male', '2023-01-29 09:06:41'),
(112, 'jah graz', 'morocco', 1990, 'jah@hah.com', '1843', 'df', 'male', '2023-02-06 17:29:58'),
(115, 'ronaldo abraham', 'angola', 1993, 'ronaldo@gmail.com', '87565', 'ng', 'male', '2023-02-06 17:30:42'),
(116, 'zhengjian yangben', 'china', 1983, 'gfjhfghfgh@ytfghj.com', 'e00000000', 'gt', 'female', '2023-02-06 17:30:56'),
(117, 'oksiao tiah', 'china', 1983, 'oksia@ytfghj.com', 'e000000001', 'google', 'female', '2023-02-06 17:31:26');

bookings表创建语句 #

CREATE TABLE `bookings` (
  `booking_id` int(11) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `user_birthYear` int(11) NOT NULL,
  `user_nationality` varchar(255) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `user_group` varchar(255) NOT NULL,
  `place_id` text DEFAULT NULL,
  `booked_by` int(11) DEFAULT NULL,
  `booked_date` date NOT NULL,
  `booked_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `booking_ref` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `status` int(11) DEFAULT NULL,
  `isDomestic` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

bookings表测试数据 #

INSERT INTO `bookings` (`booking_id`, `user_name`, `user_birthYear`, `user_nationality`, `user_id`, `user_group`, `place_id`, `booked_by`, `booked_date`, `booked_on`, `booking_ref`, `amount`, `status`, `isDomestic`) VALUES
(647, 'john abraham', 1994, 'albania', 104, 'adult', '36', 4, '2023-02-02', '2023-02-02 14:38:42', 'B-12167534871964719944', '200.00', 0, 'false'),
(648, 'zhengjian yangben', 1983, 'china', 116, 'adult', '36', 4, '2023-02-02', '2023-02-02 14:41:12', 'B-83167534874736719834', '300.00', 0, 'false'),
(649, 'zhengjian yangben', 1983, 'china', 104, 'adult', '37', 4, '2023-02-06', '2023-02-03 19:50:43', 'B-41167566360101919834', '100.00', 0, 'false'),
(650, 'john abraham', 1994, 'albania', 104, 'adult', '37', 4, '2023-02-06', '2023-02-06 06:07:03', 'B-41167566360101919834', '0.00', 0, 'false'),
(651, 'john abraham', 1994, 'albania', 116, 'adult', '37', 4, '2023-02-06', '2023-02-06 06:07:44', 'B-54167566365174419944', '0.00', 0, 'false'),
(652, 'zhengjian yangben', 1983, 'china', 116, 'adult', '37', 4, '2023-02-06', '2023-02-06 06:07:44', 'B-54167566365174419944', '0.00', 0, 'false'),
(653, 'john abraham', 1994, 'albania', 104, 'adult', '36', 4, '2023-02-02', '2023-02-02 14:38:42', 'B-12167534871964719944', '200.00', 0, 'false'),
(654, 'john abraham', 1994, 'albania', 104, 'adult', '36', 4, '2023-02-02', '2023-01-01 14:38:42', 'B-12167534871964719944', '200.00', 0, 'false');

已尝试的SQL代码 #

SELECT
`user_nationality` AS `Nationality`,
COUNT(`user_id`) AS `usersCount`,
MONTHNAME(`addedOn`) AS `monthName`,
MONTH(`addedOn`) AS `month`
FROM `users`
WHERE DATE_FORMAT(`addedOn`,'2023-%m') = DATE_FORMAT(`addedOn`,'2023-%m')
GROUP BY `monthName`,`Nationality`
ORDER BY `month`

解决方案 #

直接关联两张表会因一个用户对应多个订单，导致用户统计数重复计算、金额累加错误。正确做法是先分别对两张表聚合统计，再关联结果。

最终查询SQL #

SELECT
    u.Nationality,
    u.usersCount,
    u.monthName,
    u.month,
    COALESCE(b.totalAmount, 0.00) AS totalAmount
FROM (
    -- 统计每月每国家的注册用户数
    SELECT
        user_nationality AS Nationality,
        COUNT(DISTINCT user_id) AS usersCount,
        MONTHNAME(addedOn) AS monthName,
        MONTH(addedOn) AS month,
        YEAR(addedOn) AS year
    FROM users
    WHERE YEAR(addedOn) = 2023 -- 指定目标年份
    GROUP BY year, month, monthName, Nationality
) u
LEFT JOIN (
    -- 统计每月每国家的订单总金额
    SELECT
        user_nationality AS Nationality,
        SUM(amount) AS totalAmount,
        MONTH(booked_on) AS month,
        YEAR(booked_on) AS year
    FROM bookings
    WHERE YEAR(booked_on) = 2023 -- 指定目标年份
    GROUP BY year, month, Nationality
) b
ON u.year = b.year 
AND u.month = b.month 
AND u.Nationality = b.Nationality
ORDER BY u.year, u.month;

说明 #

1. 子查询u按年份、月份、国家统计注册用户数，用COUNT(DISTINCT user_id)避免用户重复计数；
2. 子查询b按年份、月份、国家统计订单总金额；
3. LEFT JOIN保证无订单的国家-月份组合仍能显示用户数据，COALESCE将空金额转为0；
4. 用YEAR()指定年份，减少全表扫描，提升查询效率。

内容的提问来源于stack exchange，提问作者finoco