149 lines
5.7 KiB
Plaintext
149 lines
5.7 KiB
Plaintext
{
|
|
"cells": [
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stderr",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"C:\\Users\\Admin\\AppData\\Local\\Temp\\ipykernel_2152\\2535791683.py:19: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
|
|
" order_df = pd.read_sql(query, db.con)\n"
|
|
]
|
|
},
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
" DATE_FORMAT(order_date,'%Y-%m-%d') order_id \\\n",
|
|
"0 2024-08-01 240801000602903 \n",
|
|
"1 2024-08-01 240801000802198 \n",
|
|
"2 2024-08-01 240801001202641 \n",
|
|
"3 2024-08-01 240801001402908 \n",
|
|
"4 2024-08-01 240801001414785 \n",
|
|
"... ... ... \n",
|
|
"143421 2025-01-20 250120235520923 \n",
|
|
"143422 2025-01-20 250120235602837 \n",
|
|
"143423 2025-01-20 250120235616266 \n",
|
|
"143424 2025-01-20 250120235627823 \n",
|
|
"143425 2025-01-20 250120235802366 \n",
|
|
"\n",
|
|
" order_price_dollar order_freight_price_dollar order_cate \n",
|
|
"0 201.66 64.30 家具 \n",
|
|
"1 420.65 53.60 家具 \n",
|
|
"2 51.93 13.66 家具 \n",
|
|
"3 2099.55 112.20 家具 \n",
|
|
"4 154.47 39.78 灯具 \n",
|
|
"... ... ... ... \n",
|
|
"143421 496.54 50.00 家具 \n",
|
|
"143422 121.50 17.02 灯具 \n",
|
|
"143423 281.34 9.66 家具 \n",
|
|
"143424 312.36 73.47 家具 \n",
|
|
"143425 117.39 17.21 灯具 \n",
|
|
"\n",
|
|
"[143426 rows x 5 columns]\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"from utils.gtools import MySQLconnect\n",
|
|
"import pandas as pd\n",
|
|
"# 先找出去年八月之后的订单号，订单时间，订单费用，订单所属条目，订单物流成本\n",
|
|
"with MySQLconnect('ods') as db:\n",
|
|
" query = \"\"\"SELECT\n",
|
|
" DATE_FORMAT( order_date, '%Y-%m-%d' ),\n",
|
|
" order_id,\n",
|
|
" order_price_dollar,\n",
|
|
" order_cate,\n",
|
|
" SUM(pfi.package_fund + pfi.head_way_express_fee + pfi.other_expend + pfi.indemnity)/7 AS `物流成本` \n",
|
|
" FROM\n",
|
|
" ods.order_list ol\n",
|
|
" LEFT JOIN order_express oe ON ol.order_id = oe.`单号`\n",
|
|
" LEFT JOIN package_fee_info pfi ON oe.包裹号 = pfi.package \n",
|
|
" WHERE\n",
|
|
" order_date >= '2024-08-01' \n",
|
|
" AND order_date <= '2025-01-21' \n",
|
|
" AND fund_status NOT REGEXP '等待|退款|失败|冻结' \n",
|
|
" AND site_name = 'Litfad' \n",
|
|
" GROUP BY\n",
|
|
" order_id\n",
|
|
" \"\"\"\n",
|
|
" order_df = pd.read_sql(query, db.con)\n",
|
|
" print(order_df)"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# 取采购价\n",
|
|
"order_id = order_df['order_id'].tolist()\n",
|
|
"order_ids = ','.join(f\"'{i}'\" for i in order_id)\n",
|
|
"with MySQLconnect('ods') as db:\n",
|
|
" query = f\"\"\"\n",
|
|
"with t1 AS (SELECT LEFT\n",
|
|
" ( ol.out_detials_outlink_id, 15 ) AS order_id,\n",
|
|
" SUM( out_detials_qty * price )/ 7 AS instock_cost,\n",
|
|
"\t\t\t\tNULL AS buy_cost\n",
|
|
" FROM\n",
|
|
" ods.outstock_list ol\n",
|
|
" JOIN ods.instock_list il ON ol.store_in_id = il.id \n",
|
|
" WHERE\n",
|
|
" LEFT ( ol.out_detials_outlink_id, 15 ) IN ({order_ids}) \n",
|
|
" GROUP BY\n",
|
|
" LEFT ( ol.out_detials_outlink_id, 15 )\n",
|
|
"UNION ALL\n",
|
|
" SELECT\n",
|
|
" LEFT ( order_product_id, 15 ) as order_id, \n",
|
|
"\t\t\t\tNULL as instock_cost,\n",
|
|
"\t\t\t\tSUM(buy_num * actual_price)/ 7 AS buy_cost\n",
|
|
" FROM\n",
|
|
" `warehouse_purchasing`\n",
|
|
" WHERE\n",
|
|
" LEFT ( order_product_id, 15 ) IN ({order_ids}) \n",
|
|
" AND buy_audit = \"采购完成\"\n",
|
|
" group by LEFT ( order_product_id, 15 )\n",
|
|
"\t\t)\n",
|
|
"\t\t\n",
|
|
"\tSELECT\n",
|
|
"\torder_id,\n",
|
|
"\tSUM(CASE \n",
|
|
"\tWHEN instock_cost is null THEN\n",
|
|
"\t\tbuy_cost\n",
|
|
"\tELSE\n",
|
|
"\t\tinstock_cost END) AS pur_cost\n",
|
|
"\tFROM\n",
|
|
"\tt1 \n",
|
|
"\tGROUP BY order_id\n",
|
|
"\t\n",
|
|
"\"\"\"\n"
|
|
]
|
|
}
|
|
],
|
|
"metadata": {
|
|
"kernelspec": {
|
|
"display_name": "base",
|
|
"language": "python",
|
|
"name": "python3"
|
|
},
|
|
"language_info": {
|
|
"codemirror_mode": {
|
|
"name": "ipython",
|
|
"version": 3
|
|
},
|
|
"file_extension": ".py",
|
|
"mimetype": "text/x-python",
|
|
"name": "python",
|
|
"nbconvert_exporter": "python",
|
|
"pygments_lexer": "ipython3",
|
|
"version": "3.11.5"
|
|
}
|
|
},
|
|
"nbformat": 4,
|
|
"nbformat_minor": 2
|
|
}
|