力扣之1336.每次访问的交易次数

• 题目：

• 

• 

• 

• sql建表语句：

• Create table If Not Exists Visits (user_id int, visit_date date);
  Create table If Not Exists Transactions (user_id int, transaction_date date, amount int);
  Truncate table Visits;
  insert into Visits (user_id, visit_date) values ('1', '2020-01-01');
  insert into Visits (user_id, visit_date) values ('2', '2020-01-02');
  insert into Visits (user_id, visit_date) values ('12', '2020-01-01');
  insert into Visits (user_id, visit_date) values ('19', '2020-01-03');
  insert into Visits (user_id, visit_date) values ('1', '2020-01-02');
  insert into Visits (user_id, visit_date) values ('2', '2020-01-03');
  insert into Visits (user_id, visit_date) values ('1', '2020-01-04');
  insert into Visits (user_id, visit_date) values ('7', '2020-01-11');
  insert into Visits (user_id, visit_date) values ('9', '2020-01-25');
  insert into Visits (user_id, visit_date) values ('8', '2020-01-28');
  Truncate table Transactions;
  insert into Transactions (user_id, transaction_date, amount) values ('1', '2020-01-02', '120');
  insert into Transactions (user_id, transaction_date, amount) values ('2', '2020-01-03', '22');
  insert into Transactions (user_id, transaction_date, amount) values ('7', '2020-01-11', '232');
  insert into Transactions (user_id, transaction_date, amount) values ('1', '2020-01-04', '7');
  insert into Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '33');
  insert into Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '66');
  insert into Transactions (user_id, transaction_date, amount) values ('8', '2020-01-28', '1');
  insert into Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '99');

• 分析，刚看到这道题的时候，读题读了好几遍，有点难看懂，其实这道题的难点就是怎么遍历的输出出现的次数，这里首先我先想到了递归，但是呢，我刚开始的时候没有看清楚那个最大值，一直不知道这个递归的条件怎么写，最后才发现是访问的最大次数，并不是访问出现的最大次数，这里我们可以首先算出最大的访问次数，可以直接按照Transactions 表中user_id, transaction_date分组，然后算出count排序，取出最大值，然后左连接两个表，算出每个用户每天访问的次数，然后在按照次数分组，算出每个次数出现的次数，然后再与递归的表进行左连接，把为空的值换成零，就完成了。图表分析：

• 

• 

• sql实现：

• 
  WITH RECURSIVE
      numbers AS (SELECT 0 AS num
                  UNION ALL
                  SELECT num + 1
                  FROM numbers
                  WHERE num < (select count(user_id) nu
                               from Transactions
                               group by user_id, transaction_date
                               order by nu desc
                               limit 1) -- 递归输出新的表
      ),
      t1 as (select v1.user_id, v1.visit_date, count(t.transaction_date) cou
             from Visits v1
                      left join Transactions T on v1.user_id = T.user_id and v1.visit_date = T.transaction_date
             group by v1.user_id, v1.visit_date), -- 连接两个表，算出每个人每天的次数
      t2 as (select cou, count(1) num
             from t1
             group by cou)
  select n1.num transactions_count, ifnull(t2.num, 0) visits_count -- 算出每个次数出现的次数
  from numbers n1
           left join t2 on n1.num = t2.cou
  order by transactions_count  -- 连接递归表和出现次数的表，然后把空值换成0，按照transactions_count 排序

• pandas例子代码：

• data = [[1, '2020-01-01'], [2, '2020-01-02'], [12, '2020-01-01'], [19, '2020-01-03'], [1, '2020-01-02'], [2, '2020-01-03'], [1, '2020-01-04'], [7, '2020-01-11'], [9, '2020-01-25'], [8, '2020-01-28']]
  visits = pd.DataFrame(data, columns=['user_id', 'visit_date']).astype({'user_id':'Int64', 'visit_date':'datetime64[ns]'})
  data = [[1, '2020-01-02', 120], [2, '2020-01-03', 22], [7, '2020-01-11', 232], [1, '2020-01-04', 7], [9, '2020-01-25', 33], [9, '2020-01-25', 66], [8, '2020-01-28', 1], [9, '2020-01-25', 99]]
  transactions = pd.DataFrame(data, columns=['user_id', 'transaction_date', 'amount']).astype({'user_id':'Int64', 'transaction_date':'datetime64[ns]', 'amount':'Int64'})

• pandas分析：

• 我们首先先连接两个表，使用左连接，连接条件为user_id=user_id，visit_date=transaction_date，然后我们再按照user_id和visit_date分组，然后在算出transaction_date的值，会分别算出每个人每天的次数，然后我们在按照次数分组，算出每个次数有多少次，然后找出次数最大值，然后建一个新的dataframe对象，新增一列使用for循环添加数据，循环条件是最大值，然后再把这两个表通过次数左连接，然后给空值替换成0，就完成了这道题

• pandas实现：

• import pandas as pd
  
  def draw_chart(visits: pd.DataFrame, transactions: pd.DataFrame) -> pd.DataFrame:
      a=pd.merge(visits,transactions,left_on=['user_id','visit_date'],right_on=['user_id','transaction_date'],how='left')   -- 连接两个表，使用左连接，连接条件为user_id=user_id，visit_date=transaction_date
  
      b=a.groupby(['user_id','visit_date'])['transaction_date'].count().reset_index()  -- 按照'user_id','visit_date'分组，算出每个组内的transaction_date的个数
  
      c=b.groupby(['transaction_date'])['user_id'].count().reset_index() -- 按照每个人的次数分组，算出每个次数出现的次数
  
      ee=pd.DataFrame()  -- 创建一个新的dataframe对象
      ee['transactions_count']=[i for i in range(c['transaction_date'].max()+1)]     -- 添加一列，值为次数出现的最大值的循环                    
      o=pd.merge(ee,c,left_on='transactions_count',right_on='transaction_date',how='left').fillna(0).sort_values(['transactions_count'])  -- 连接两个表
      o=o[['transactions_count','user_id']] -- 取出所需要的两列
      o.columns=['transactions_count','visits_count']  -- 修改该列名
      return o

原文地址：https://blog.csdn.net/qq_46038718/article/details/142748388

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！