-- 1. 基本查询
SELECT column1, column2 
FROM table_name 
WHERE condition;

-- 2. 排序
SELECT * 
FROM sales 
ORDER BY amount DESC;

-- 3. 分组聚合
SELECT category, COUNT(*) as count, SUM(amount) as total
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- 4. 多表连接
SELECT o.order_id, c.name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2023-01-01';

-- 5. 子查询
SELECT *
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 6. 窗口函数
SELECT 
    name, 
    department, 
    salary,
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

import pandas as pd

# 读取CSV文件
df_csv = pd.read_csv('data.csv')

# 读取Excel文件
df_excel = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# 读取JSON文件
df_json = pd.read_json('data.json')

# 导出CSV
df_csv.to_csv('output.csv', index=False)

# 导出Excel
df_excel.to_excel('output.xlsx', sheet_name='Data', index=False)

# 导出JSON
df_json.to_json('output.json', orient='records')

import pandas as pd
import numpy as np
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import train_test_split
from sklearn.metrics import mean_squared_error, r2_score

# 创建示例数据
np.random.seed(42)
data = pd.DataFrame({
    '广告投入': np.linspace(1, 10, 50),
    '销售额': np.linspace(10, 100, 50) + np.random.normal(0, 5, 50)
})

X = data[['广告投入']].values
y = data['销售额'].values

# 划分训练集和测试集
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# 创建并训练模型
model = LinearRegression()
model.fit(X_train, y_train)

# 预测
y_pred = model.predict(X_test)

# 评估模型
mse = mean_squared_error(y_test, y_pred)
r2 = r2_score(y_test, y_pred)

print(f"回归系数: {model.coef_[0]:.2f}")
print(f"截距: {model.intercept_:.2f}")
print(f"MSE: {mse:.2f}")
print(f"R²: {r2:.2f}")

import pandas as pd
import numpy as np
from sklearn.cluster import KMeans

# 创建示例数据
np.random.seed(42)
data = pd.DataFrame({
    '消费金额': np.random.randint(100, 1000, 100),
    '购买频率': np.random.randint(1, 20, 100)
})

# 使用肘部法确定最佳K值
inertia = []
for k in range(1, 11):
    kmeans = KMeans(n_clusters=k, random_state=42)
    kmeans.fit(data)
    inertia.append(kmeans.inertia_)

print("不同K值的惯性:", inertia[:5])

# 使用K=3进行聚类
kmeans = KMeans(n_clusters=3, random_state=42)
data['聚类标签'] = kmeans.fit_predict(data)

print("\n聚类中心:")
print(kmeans.cluster_centers_)

import pandas as pd
import numpy as np

# 创建时间序列数据
dates = pd.date_range(start='2023-01-01', periods=12, freq='M')
sales = np.array([100, 120, 110, 130, 140, 160, 150, 170, 180, 190, 200, 220])
ts_data = pd.DataFrame({'日期': dates, '销售额': sales})
ts_data.set_index('日期', inplace=True)

# 计算移动平均
ts_data['MA3'] = ts_data['销售额'].rolling(window=3).mean()
ts_data['MA6'] = ts_data['销售额'].rolling(window=6).mean()

print("时间序列数据:")
print(ts_data)

# 计算增长率
ts_data['增长率'] = ts_data['销售额'].pct_change() * 100
print("\n增长率:")
print(ts_data['增长率'])

import pandas as pd
import numpy as np

# 读取电商用户行为数据
df = pd.read_csv('user_behavior.csv')

# 数据预处理
df['timestamp'] = pd.to_datetime(df['timestamp'])
df['date'] = df['timestamp'].dt.date

# 1. 用户访问分析
daily_visitors = df.groupby('date')['user_id'].nunique()
print("每日访问用户数:\n", daily_visitors)

# 2. 转化率分析
total_visitors = df['user_id'].nunique()
buyers = df[df['behavior'] == 'buy']['user_id'].nunique()
conversion_rate = buyers / total_visitors * 100
print(f"\n转化率: {conversion_rate:.2f}%")

# 3. 用户购买频次分析
purchase_counts = df[df['behavior'] == 'buy'].groupby('user_id').size()
print("\n用户购买频次分布:")
print(purchase_counts.value_counts())

# 4. RFM分析
current_date = df['date'].max() + pd.Timedelta(days=1)
rfm = df[df['behavior'] == 'buy'].groupby('user_id').agg({
    'date': lambda x: (current_date - x.max()).days,
    'user_id': 'count',
    'amount': 'sum'
}).rename(columns={
    'date': 'R',
    'user_id': 'F',
    'amount': 'M'
})

print("\nRFM分析结果:")
print(rfm.head())

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import accuracy_score, classification_report

# 读取信用数据
df = pd.read_csv('credit_data.csv')

# 特征选择
X = df[['收入', '负债', '信用历史', '贷款金额', '还款期限']]
y = df['违约']

# 划分数据集
X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)

# 创建模型
model = RandomForestClassifier(n_estimators=100, random_state=42)
model.fit(X_train, y_train)

# 预测
y_pred = model.predict(X_test)

# 评估
accuracy = accuracy_score(y_test, y_pred)
print(f"准确率: {accuracy:.2%}")
print("\n分类报告:")
print(classification_report(y_test, y_pred))

# 特征重要性
feature_importance = pd.DataFrame({
    '特征': X.columns,
    '重要性': model.feature_importances_
}).sort_values('重要性', ascending=False)

print("\n特征重要性:")
print(feature_importance)

import pandas as pd

# 读取营销数据
df = pd.read_csv('marketing_data.csv')

# 渠道效果分析
channel_stats = df.groupby('渠道').agg({
    '花费': 'sum',
    '转化': 'sum',
    '收入': 'sum'
}).reset_index()

# 计算关键指标
channel_stats['转化率'] = channel_stats['转化'] / channel_stats['花费'] * 100
channel_stats['ROI'] = (channel_stats['收入'] - channel_stats['花费']) / channel_stats['花费'] * 100
channel_stats['CAC'] = channel_stats['花费'] / channel_stats['转化']

print("各渠道效果对比:")
print(channel_stats[['渠道', '花费', '转化', '收入', '转化率', 'ROI', 'CAC']])

# 转化漏斗分析
funnel = pd.DataFrame({
    '阶段': ['曝光', '点击', '访问', '注册', '购买'],
    '数量': [10000, 2000, 1500, 500, 100]
})

funnel['转化率'] = funnel['数量'] / funnel['数量'].iloc[0] * 100
funnel['阶段转化率'] = funnel['数量'] / funnel['数量'].shift(1) * 100

print("\n转化漏斗分析:")
print(funnel)