力扣之1364.顾客的可信联系人数量
-
题目：
-
-
-
sql建表语句：
-
Create table If Not Exists Customers (customer_id int, customer_name varchar(20), email varchar(30)); Create table If Not Exists Contacts (user_id int, contact_name varchar(20), contact_email varchar(30)); Create table If Not Exists Invoices (invoice_id int, price int, user_id int); Truncate table Customers; insert into Customers (customer_id, customer_name, email) values ('1', 'Alice', 'alice@leetcode.com'); insert into Customers (customer_id, customer_name, email) values ('2', 'Bob', 'bob@leetcode.com'); insert into Customers (customer_id, customer_name, email) values ('13', 'John', 'john@leetcode.com'); insert into Customers (customer_id, customer_name, email) values ('6', 'Alex', 'alex@leetcode.com'); Truncate table Contacts; insert into Contacts (user_id, contact_name, contact_email) values ('1', 'Bob', 'bob@leetcode.com'); insert into Contacts (user_id, contact_name, contact_email) values ('1', 'John', 'john@leetcode.com'); insert into Contacts (user_id, contact_name, contact_email) values ('1', 'Jal', 'jal@leetcode.com'); insert into Contacts (user_id, contact_name, contact_email) values ('2', 'Omar', 'omar@leetcode.com'); insert into Contacts (user_id, contact_name, contact_email) values ('2', 'Meir', 'meir@leetcode.com'); insert into Contacts (user_id, contact_name, contact_email) values ('6', 'Alice', 'alice@leetcode.com'); Truncate table Invoices; insert into Invoices (invoice_id, price, user_id) values ('77', '100', '1'); insert into Invoices (invoice_id, price, user_id) values ('88', '200', '1'); insert into Invoices (invoice_id, price, user_id) values ('99', '300', '2'); insert into Invoices (invoice_id, price, user_id) values ('66', '400', '2'); insert into Invoices (invoice_id, price, user_id) values ('55', '500', '13'); insert into Invoices (invoice_id, price, user_id) values ('44', '60', '6'); -
分析，这道题最难的就是理解题目，该客户的联系人，和可信联系人，这两个非常难理解，我认为该客户的联系人就是Contacts 表中的所有联系人都是，可信联系人是指Contacts 表中邮箱在客户表中的人，这里我们首先先把Contacts 中的可信联系人标记出来，如果是可信联系人就标记为1 否则标记为0，然后分组求出每个人的联系人，还有每个人的可信联系人，全部联系人，这里是用count算出，可信联系人，这里是用sum标记列算出，然后连接三个表就得出来结果了，图表分析：
-
-
-
sql实现：
-
with t1 as (select *, if(contact_email in (select email from Customers), 1, 0) ll from Contacts), -- 首先先给可信人员打上标签，可信人返回1 不可信人返回0 t2 as (select invoice_id, price, user_id, customer_name from Invoices i1, Customers c1 where i1.user_id = c1.customer_id), -- 然后连接发票表和用户表 t3 as (select user_id, count(1) contacts_cnt, sum(ll) trusted_contacts_cnt from t1 group by user_id) -- 然后算出所有联系人和可信联系人的人数 select invoice_id, customer_name, price, ifnull(contacts_cnt, 0) contacts_cnt, ifnull(trusted_contacts_cnt, 0) trusted_contacts_cnt from t2 left join t3 on t2.user_id = t3.user_id order by invoice_id -- 连接发票表和可信人数表然后人数如果为空，就返回零，否则返回人数，按照发票id排序 -
pandas例子：
-
data = [[1, 'Alice', 'alice@leetcode.com'], [2, 'Bob', 'bob@leetcode.com'], [13, 'John', 'john@leetcode.com'], [6, 'Alex', 'alex@leetcode.com']] customers = pd.DataFrame(data, columns=['customer_id', 'customer_name', 'email']).astype({'customer_id':'Int64', 'customer_name':'object', 'email':'object'}) data = [[1, 'Bob', 'bob@leetcode.com'], [1, 'John', 'john@leetcode.com'], [1, 'Jal', 'jal@leetcode.com'], [2, 'Omar', 'omar@leetcode.com'], [2, 'Meir', 'meir@leetcode.com'], [6, 'Alice', 'alice@leetcode.com']] contacts = pd.DataFrame(data, columns=['user_id', 'contact_name', 'contact_email']).astype({'user_id':'Int64', 'contact_name':'object', 'contact_email':'object'}) data = [[77, 100, 1], [88, 200, 1], [99, 300, 2], [66, 400, 2], [55, 500, 13], [44, 60, 6]] invoices = pd.DataFrame(data, columns=['invoice_id', 'price', 'user_id']).astype({'invoice_id':'Int64', 'price':'Int64', 'user_id':'Int64'}) -
pandas分析：大致与sql分析一样
-
pandas实现：
-
import pandas as pd def count_trusted_contacts(customers: pd.DataFrame, contacts: pd.DataFrame, invoices: pd.DataFrame) -> pd.DataFrame: contacts['cn']=contacts['contact_email'].apply(lambda x:1 if x in customers['email'].values else 0) -- 给联系表中的人打标签，如果是可信人返回1 否则返回零 d=contacts.groupby('user_id').agg({'contact_name':'count','cn':'sum'}).reset_index() -- 算出每个人的全部联系人和可信人数 b=invoices.merge(customers,left_on='user_id',right_on='customer_id',how='left') -- 连接发票表和客户表 c=b.merge(d,left_on='user_id',right_on='user_id',how='left') -- 连接发票姓名表和联系人数和可信人数表 c=c[['invoice_id','customer_name','price','contact_name','cn']].fillna(0).sort_values('invoice_id') -- 把空值设置成0，然后排序 c.columns=['invoice_id','customer_name','price','contacts_cnt','trusted_contacts_cnt'] -- 修改列名 return c
原文地址：https://blog.csdn.net/qq_46038718/article/details/142833835
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！