本文介绍了LeetCode上的两道题目——市场分析I和II，涉及用户购买行为统计和商品偏好分析。通过解析JSON数据并转化为MySQL INSERT语句，建立数据库并填充数据。针对每道题目，提出了两种不同的SQL解决方案，包括子查询和外连接，讨论了它们的时间复杂度和适用场景。此外，还展示了如何使用Python解析JSON数据。
Leetcode-1158、1158 市场分析I 、II
Apare_xzc
题目链接：
表的定义
两道题的表是一样的。有三张表：Users, Orders, Items。
Itmes表在1158中其实没有用到
建库建表
为了方便本地调试，我们可以建库建表(拿去不谢)
-- Author: Apare_xzc
DROP DATABASE IF EXISTS leetcode1158;
CREATE DATABASE leetcode1158;
use leetcode1158;
CREATE TABLE `USERS` (
`user_id` int NOT NULL,
`join_date` date DEFAULT NULL,
`favorite_brand` char(20) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `ORDERS` (
`order_id` int NOT NULL,
`order_date` date DEFAULT NULL,
`item_id` int DEFAULT NULL,
`buyer_id` int DEFAULT NULL,
`seller_id` int DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `ITEMS` (
`item_id` int NOT NULL,
`item_brand` varchar(20) DEFAULT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据
Leetcode的数据是以Json形式给出的。我们可以用一段Python代码来解析成MySQL insert语句。如何解析可以我这篇博客中也提到了。下面直接给出我们的json解析代码：
#!/usr/bin/python3
"""
Authon: Apare_xzc
Convert the input JSON data into the insert statement of MySQL
"""
import json;
from datetime import datetime, date
def get_insert_sql_from_json(db_json):
#db_obj = json.loads(db_json,cls=JsonToDatetime)
db_obj = json.loads(db_json)
headers = db_obj.get("headers")
rows = db_obj.get("rows")
for tb_name in rows.keys():
print ('DELETE FROM ' + tb_name + ';')
column_names = headers.get(tb_name)
column_count = len(column_names)
if column_count == 0:
continue
all_rows = rows.get(tb_name)
line1 = 'INSERT INTO ' + tb_name + '(' + column_names[0]
for i in range(1, column_count):
line1 += ', ' + column_names[i]
line1 += ') VALUES'
print (line1)
row_count = len(all_rows)
for i in range(0, row_count):
one_row = all_rows[i]
if len(one_row) != column_count:
continue
line2 = '('
line2 += '"'+one_row[0]+'"' if type(one_row[0]) == type('') else str(one_row[0])
for j in range(1, column_count):
line2 += ', '
line2 += '"'+one_row[j]+'"' if type(one_row[j]) == type('') else str(one_row[j])
line2 += ');' if i == row_count-1 else '),'
print (' ',line2)
if __name__ == "__main__":
db_json = input()
get_insert_sql_from_json(db_json)
于是，对于Leetcode 1158样例的输入：
{“headers”:{“Users”:[“user_id”,“join_date”,“favorite_brand”],“Orders”:[“order_id”,“order_date”,“item_id”,“buyer_id”,“seller_id”],“Items”:[“item_id”,“item_brand”]},“rows”:{“Users”:[[1,“2018-01-01”,“Lenovo”],[2,“2018-02-09”,“Samsung”],[3,“2018-01-19”,“LG”],[4,“2018-05-21”,“HP”]],“Orders”:[[1,“2019-08-01”,4,1,2],[2,“2018-08-02”,2,1,3],[3,“2019-08-03”,3,2,3],[4,“2018-08-04”,1,4,2],[5,“2018-08-04”,1,3,4],[6,“2019-08-05”,2,2,4]],“Items”:[[1,“Samsung”],[2,“Lenovo”],[3,“LG”],[4,“HP”]]}}
我们可以得到如下INSERT语句
DELETE FROM Users;
INSERT INTO Users(user_id, join_date, favorite_brand) VALUES
(1, "2018-01-01", "Lenovo"),
(2, "2018-02-09", "Samsung"),
(3, "2018-01-19", "LG"),
(4, "2018-05-21", "HP");
DELETE FROM Orders;
INSERT INTO Orders(order_id, order_date, item_id, buyer_id, seller_id) VALUES
(1, "2019-08-01", 4, 1, 2),
(2, "2018-08-02", 2, 1, 3),
(3, "2019-08-03", 3, 2, 3),
(4, "2018-08-04", 1, 4, 2),
(5, "2018-08-04", 1, 3, 4),
(6, "2019-08-05", 2, 2, 4);
DELETE FROM Items;
INSERT INTO Items(item_id, item_brand) VALUES
(1, "Samsung"),
(2, "Lenovo"),
(3, "LG"),
(4, "HP");
插入数据后，我们的表变成了这个样子：
开始写SQL
我们来看1158:
题目要求：
方法一：暴力“两重for循环”
因为我们要查找的是每个用户作为买家的在2019年的订单总数。注册日期我们在User表中可以直接得到。订单总数如何得到呢？
# python伪代码
print ("buyer_id", "join_date", "orders_in_2019")
for user in Users:
order_count = 0
for order in Orders:
if order.buyer_id == user.user_id && Year(order.date) == 2019:
order_count += 1
print (user.user_id, user.joint_date, order_count)
我们每个人，都从所有订单中查询一遍，如果是自己的，又是2019年，那么就计数O(n2)
mysql代码一
SELECT user_id AS buyer_id, join_date, (
SELECT COUNT(*) FROM Orders o
WHERE o.buyer_id = u.user_id
AND Year(order_date) = 2019
) AS orders_in_2019 FROM Users u;
方法二：直接对Orders表进行group by，按照seller_id分组，然后和Users外连接。
# python伪代码
print ("buyer_id", "join_date", "orders_in_2019")
dict_count = {}
for order in Orders:
if orders.buyer_id not in dict_count.keys():
dict_count[order.buyer_id] = 1
else:
dict_count[order.buyer_id] += 1
for user in Users:
print (user.user_id, user.join_date, dict_count.get(user_id,default = 0)
显然复杂度是O(n) 如果group by 用的是哈希表
理论上更快
mysql代码二
SELECT user_id AS 'buyer_id', join_date, Ifnull(cnt,0) AS 'orders_in_2019'
FROM Users u LEFT OUTER JOIN (
SELECT buyer_id, COUNT(*) AS cnt
FROM orders
WHERE Year(order_date) = 2019
GROUP BY buyer_id
) t
ON u.user_id = t.buyer_id;
ifnull(cnt, 0) 这个函数的作用是，如果cnt是null，就返会0， 否则返回cnt
我们来看1159
题干
获得样例的INSERT语句
{“headers”:{“Users”:[“user_id”,“join_date”,“favorite_brand”],“Orders”:[“order_id”,“order_date”,“item_id”,“buyer_id”,“seller_id”],“Items”:[“item_id”,“item_brand”]},“rows”:{“Users”:[[1,“2019-01-01”,“Lenovo”],[2,“2019-02-09”,“Samsung”],[3,“2019-01-19”,“LG”],[4,“2019-05-21”,“HP”]],“Orders”:[[1,“2019-08-01”,4,1,2],[2,“2019-08-02”,2,1,3],[3,“2019-08-03”,3,2,3],[4,“2019-08-04”,1,4,2],[5,“2019-08-04”,1,3,4],[6,“2019-08-05”,2,2,4]],“Items”:[[1,“Samsung”],[2,“Lenovo”],[3,“LG”],[4,“HP”]]}}
DELETE FROM Users;
INSERT INTO Users(user_id, join_date, favorite_brand) VALUES
(1, "2019-01-01", "Lenovo"),
(2, "2019-02-09", "Samsung"),
(3, "2019-01-19", "LG"),
(4, "2019-05-21", "HP");
DELETE FROM Orders;
INSERT INTO Orders(order_id, order_date, item_id, buyer_id, seller_id) VALUES
(1, "2019-08-01", 4, 1, 2),
(2, "2019-08-02", 2, 1, 3),
(3, "2019-08-03", 3, 2, 3),
(4, "2019-08-04", 1, 4, 2),
(5, "2019-08-04", 1, 3, 4),
(6, "2019-08-05", 2, 2, 4);
DELETE FROM Items;
INSERT INTO Items(item_id, item_brand) VALUES
(1, "Samsung"),
(2, "Lenovo"),
(3, "LG"),
(4, "HP");
分析：我们不妨就用1158中暴力两重for的思想
# python伪代码
print (seller_id, 2nd_item_fav_brand)
dict_item = Items
for user in Users:
list_my_order = []
for order in Orders:
if order.seller_id == user.user_id:
list_my_order.append(order)
list_my_order.sort([](key = order_date)) # 按照日期排序，瞎写的语法别在意
2nd_item = 'null' if len(list_my_order) < 2 else list_my_order[1]
print (user.user_id, 'yes' if 2nd_item == user.favorite_branch else 'no'
直接上mysql代码
SELECT user_id AS 'seller_id',
if(
(SELECT item_brand
FROM Orders o INNER JOIN Items i
ON o.item_id = I.item_id
WHERE o.seller_id = u.user_id
ORDER BY order_date
LIMIT 1 OFFSET 1
) = u.favorite_brand, 'yes', 'no') AS '2nd_item_fav_brand'
FROM Users u;
if(statement, 'yes', 'no')在mysql中表示如果statement的条件表达式为真，返回’yes’，否则返回’no’
2021.2.22
xzc
扫一扫
252
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
