Quarto

# 安装核心包
install.packages(c("DBI", "duckdb", "dbplyr", "nycflights13", "dplyr"))

# 加载包（验证安装）
library(DBI)
library(duckdb)
library(dbplyr)
library(nycflights13)
library(dplyr)

# 连接（或创建）flights.db 数据库
con_flights <- DBI::dbConnect(
  drv = duckdb::duckdb(),
  dbdir = "D:/Quarto-SQL-Notebook/data/flights.db",
  read_only = FALSE
)

# 将 nycflights13 包的表写入 duckdb
dbWriteTable(con_flights, "flights", nycflights13::flights)
dbWriteTable(con_flights, "airlines", nycflights13::airlines)
dbWriteTable(con_flights, "airports", nycflights13::airports)
dbWriteTable(con_flights, "planes", nycflights13::planes)
dbWriteTable(con_flights, "weather", nycflights13::weather)

# 关闭连接
DBI::dbDisconnect(con_flights)

# 连接（或创建）superheroes.db 数据库
con_superheroes <- DBI::dbConnect(
  drv = duckdb::duckdb(),
  dbdir = "D:/Quarto-SQL-Notebook/data/superheroes.db",
  read_only = FALSE
)

# 读取并写入数据库
superheroes_data <- read.csv("D:/Quarto-SQL-Notebook/data/superheroes.csv", stringsAsFactors = FALSE)
dbWriteTable(con_superheroes, "superhero", superheroes_data)

# 关闭连接
DBI::dbDisconnect(con_superheroes)

---
title: "SQL Notebook with Quarto (Windows)"
author: "Your Name"
date: "YYYY-MM-DD"
format: html
---

## 初始化数据库连接
```{r}
#| label: setup-db-connection
#| include: false
library(DBI)
library(duckdb)

# 连接 flights.db
con_flights <- DBI::dbConnect(
  drv = duckdb::duckdb(),
  dbdir = "D:/Quarto-SQL-Notebook/data/flights.db",
  read_only = TRUE
)

# 连接 superheroes.db
con_superheroes <- DBI::dbConnect(
  drv = duckdb::duckdb(),
  dbdir = "D:/Quarto-SQL-Notebook/data/superheroes.db",
  read_only = TRUE
)

#| label: query-airlines
#| connection: con_flights
#| echo: true
#| title: 航空公司列表（前10条）

SELECT name, carrier FROM airlines LIMIT 10;

#| label: query-superhero-tables
#| connection: con_superheroes
#| echo: false
#| title: 超级英雄数据库的所有表

SHOW TABLES;

#| label: query-flight-delay
#| connection: con_flights
#| title: 各航空公司航班平均延误时间

SELECT
  airlines.name AS 航空公司,
  ROUND(MEAN(flights.dep_delay), 2) AS 平均延误分钟,
  ROUND(STDDEV(flights.dep_delay) / SQRT(COUNT(1)), 2) AS 延误标准误
FROM flights
INNER JOIN airlines ON flights.carrier = airlines.carrier
GROUP BY airlines.name
ORDER BY 平均延误分钟 DESC;

#| label: query-superhero-gender
#| connection: con_superheroes
#| title: Marvel/DC 超级英雄性别比例

WITH pub_count AS (
  SELECT
    publisher_id,
    COUNT(publisher_id) AS total_sh
  FROM superhero
  GROUP BY publisher_id
),
gender_count AS (
  SELECT
    publisher_id,
    gender_id,
    COUNT(gender_id) AS number
  FROM superhero
  GROUP BY publisher_id, gender_id
)

SELECT
  pub.publisher_name AS 出版社,
  gen.gender AS 性别,
  gc.number AS 数量,
  ROUND(100 * gc.number / pc.total_sh, 2) AS 占比(%)
FROM gender_count AS gc
INNER JOIN pub_count AS pc ON gc.publisher_id = pc.publisher_id
INNER JOIN publisher AS pub ON gc.publisher_id = pub.id
INNER JOIN gender AS gen ON gc.gender_id = gen.id
WHERE pub.publisher_name IN ('Marvel Comics', 'DC Comics')
ORDER BY 出版社, 性别;

---
# 🔹 Quarto 基础必填（可自定义）
title: "利用 Python 进行数据分析 (原书第3版)"
date: "2025-09-15"
modified: "2025-09-17"
tags: [program/python]

# 🔹 保留 DigitalGarden 原自定义链接
permalink: books/36632126/Preliminaries

# 🔹 保留原SEO描述
description: "本书第 1 版出版于 2012 年...非常适合刚开始学习 Python 的数据分析师..."

# 🔹 输出格式（html/pdf/docx 任选）
format: html
---

---
title: "利用 Python 进行数据分析 (原书第3版)"
date: "2025-09-15 16:42"
modified: "2025-09-17 15:19"
tags: [program/python]
permalink: books/36632126/Preliminaries
description: "本书第 1 版出版于 2012 年...非常适合刚开始学习 Python 的数据分析师..."

# 🔹 社交分享配置
opengraph:
  site_name: DavonOs
  type: book
  image: https://i-blog.csdnimg.cn/direct/a36631c7292b546cc8982429c96df4bb4.png
  image_alt: bookcover

# 🔹 网页格式配置
format:
  html:
    toc: true
---

---
title: "利用 Python 进行数据分析 (原书第3版)"
author: "Wes McKinney"
date: "2025-09-15"
tags: [program/python]
description: "Python 数据分析经典书籍笔记"

# 输出为 PDF / Word
format:
  pdf:
    toc: true
    number-sections: true
---

import re
from pathlib import Path

# 👉 配置你的笔记文件夹路径
OBSIDIAN_FOLDER = Path("/你的Obsidian仓库路径")

# 要删除的 dg 字段
REMOVE_FIELDS = [
    r"publish: true.*",
    r"dg-show-inline-title:.*",
    r"dg-metatags:",
    r"og:.*"
]
remove_pattern = re.compile("|".join(REMOVE_FIELDS), re.MULTILINE)

# 批量处理所有 md/qmd 文件
for file in OBSIDIAN_FOLDER.rglob("*.md") + OBSIDIAN_FOLDER.rglob("*.qmd"):
    content = file.read_text(encoding="utf-8")
    
    # 1. 删除无用字段
    content = remove_pattern.sub("", content)
    # 2. 替换时间字段
    content = content.replace("dg-created:", "date:")
    content = content.replace("dg-updated:", "modified:")
    # 3. 清理空行
    content = re.sub(r"\n{3,}", "\n\n", content)
    
    file.write_text(content, encoding="utf-8")
    print(f"已处理: {file.name}")

---
title: "我的自定义页面"
aliases:
  - /custom-slug/  # 主自定义 URL
  - /old-page-url.html  # 旧 URL 重定向
---

website:
  pages:
    - href: about.qmd
      aliases: [/about-us/, /company-info/]

# 发布到 Quarto Pub 后
quarto pub list  # 查看已发布站点
# 访问 https://quartopub.com 管理界面修改 slug