如何从这些Dataframe中提取这些sql查询？

ckx4rj1h 于 2021-07-27 发布在 Java

关注(0)|答案(1)|浏览(201)

sql查询

customers = pd.DataFrame({'customer_id': {0: 5386596, 1: 32676876}, 'created_at': {0: Timestamp('2017-01-27 00:00:00'), 1: Timestamp('2018-06-07 00:00:00')}, 'venture_code': {0: 'MY', 1: 'ID'}})

visits = Pd.DataFrame({'customer_id': {0: 3434886, 1: 10053}, 'date': {0: Timestamp('2016-10-02 00:00:00'), 1: Timestamp('2017-12-14 00:00:00')}})

orders = Pd.DataFrame({'order_id': {0: 112525, 1: 112525}, 'date': {0: Timestamp('2019-02-01 00:00:00'), 1: Timestamp('2019-02-01 00:00:00')}, 'sku': {0: 'SA108SH89OLAHK', 1: 'RO151AA60REHHK'}, 'customer_id': {0: 46160566, 1: 46160566}})

products = Pd.DataFrame({'sku': {0: 'SA108SH89OLAHK', 1: 'RO151AA60REHHK'}, 'brand': {0: 1, 1: 1}, 'supplier': {0: 'A', 1: 'B'}, 'category': {0: 'Mapp', 1: 'Macc'}, 'price': {0: 15, 1: 45}})

segment = Pd.DataFrame({'Age Range': {0: '<20', 1: '<20'},
 'Gender': {0: 'female', 1: 'female'},
 'Category': {0: 'Wsho', 1: 'Wapp'},
 'Discount %': {0: 0.246607432, 1: 0.174166503},
 'NMV': {0: 2509.580375, 1: 8910.447587},
 '# Items': {0: 169, 1: 778},
 '# Orders': {0: 15, 1: 135}})

buying = Pd.DataFrame({'Supplier Name': {0: 'A', 1: 'A'},
 'Brand Name': {0: 1, 1: 2},
 '# SKU': {0: 506, 1: 267},
 '# Item Before Return': {0: 5663, 1: 3256},
 '# Item Returned': {0: 2776, 1: 1395},
 'Margin %': {0: 0.266922793, 1: 0.282847894},
 'GMV': {0: 191686.749171408, 1: 115560.037075292}})

使用sql或pandas，请告诉我如何
1.比较2019年第四季度所有国家/地区的月度销售额（gmv）趋势（风险企业代码）
2.根据总销售额（gmv）显示每个产品类别的前10个品牌
我写了，但问错了！

SELECT category, SUM(GMV) as Total_Sales FROM products INNER JOIN buying ON products.brand = buying.[Brand Name]

sql python DataFrame pandas

来源：https://stackoverflow.com/questions/63157862/how-do-i-extract-these-sql-queries-from-these-pandas-dataframes

1条答案

按热度按时间

cqoc49vn1#

关于这个错误，列名中有一个空格。
在sql中，如果列有空格，请使用括号将列名括起来：

MyTable.[My Column]

在代码中，使用以下sql：

SELECT category, SUM(GMV) as Total_Sales FROM products INNER JOIN buying ON products.brand = buying.[Brand Name]

我无法访问您的数据，因此无法进行测试，但我认为这些查询是正确的。你可能需要调整一下。
第1部分：

select c.venture_code, sum(b.GMV) GMVSum from customers c join orders o on c.customer_id = o.customer_id 
   join products p on o.skuv=p.sku
   join buying b on p.brand = b.[Brand Name] and p.supplier = b.[Supplier Name]
   where o.date >= '2019-10-01' and o.date <= '2019-12-31'  -- 2019 4th qtr
   group by c.venture_code

第2部分：

select * from 
(select *, RANK() over (PARTITION BY category,brand order by GMV) rk from
(select p.brand, p.category, b.GMV from products p join buying b on p.brand = b.[Brand Name] and p.supplier = b.[Supplier Name]) x) xx
where rk <= 10

赞(0）回复(0）举报 2021-07-27

我来回答

如何从这些Dataframe中提取这些sql查询？

1条答案

相关问题

热门标签

最新问答