关于mysql:从订单信息表order-info中查询首次下单后第二天仍然下单的用户占所有下单用户的比例结果保留一位小数使用百分数显示

23次阅读

共计 842 个字符，预计需要花费 3 分钟才能阅读完成。

SELECT ROUND((COUNT(DISTINCT second_day_users.user_id) / COUNT(DISTINCT first_day_users.user_id)) * 100, 1) AS conversion_rate
FROM (
    SELECT user_id
    FROM `order`
    WHERE DATEDIFF(create_date, (SELECT MIN(create_date) FROM `order`)) = 1
) second_day_users
right JOIN (
    SELECT user_id
    FROM `order`
    WHERE create_date = (SELECT MIN(create_date) FROM `order`)
) first_day_users
ON second_day_users.user_id = first_day_users.user_id;

INSERT INTO `order` (order_id, user_id, create_date, total_amount) SELECT
1,
101,
'2021-09-27',
29000.00 UNION ALL
SELECT
 2,
 103,
 '2020-10-02',
 28000.00 UNION ALL
SELECT
 3,
 101,
 '2021-09-28',
 29000.00 UNION ALL
SELECT
 4,
 101,
 '2021-09-30',
 29000.00 UNION ALL
SELECT
 5,
 103,
 '2020-10-03',
 28000.00 UNION ALL
SELECT
 6,
 103,
 '2020-10-04',
 28000.00 UNION ALL
SELECT
 7,
 104,
 '2020-10-02',
 28000.00 UNION ALL
SELECT
 8,
 104,
 '2020-10-02',
 28000.00 UNION ALL
SELECT
 9,
 104,
 '2020-10-03',
 28000.00 UNION ALL
SELECT
 10,
 104,
 '2020-10-04',
 28000.00

正文完