我有一个SQL查询:
pd.read_sql_query("""SELECT UvTab.A, UvTab.Uv,
IFNULL(DvTab.Dv, 0) AS Dv
FROM
(
SELECT A, COUNT(*) AS Uv FROM B
WHERE Vtype = 2 GROUP BY A
) AS UvTab
LEFT JOIN
(
SELECT A, COUNT(*) AS Dv FROM B
WHERE Vtype = 3 GROUP BY A
) AS DvTab
ON UvTab.A = DvTab.A
""", conn)
我的目标是得到同样的结果,但只使用熊猫的方法。我得到的是:
UvTab = B.loc[B.Vtype == 2].groupby("A").size()
UvTab = pd.DataFrame({'A' : UvTab.index, 'Uv' : UvTab.values})
DvTab = B.loc[B.Vtype == 3].groupby("A").size()
DvTab = pd.DataFrame({'A' : DvTab.index, 'Dv' : DvTab.values})
df = pd.merge(UvTab, DvTab, how='left', on='A')
df['Dv'] = df['Dv'].fillna(0)
看起来很好。但这是表示查询的最简单和最好的方法吗
一种想法是聚合} :
sum
进行计数匹配,然后使用^{或者用
merge
替代:相关问题 更多 >
编程相关推荐