7 DuckDB SQL 쿼리는 몇 시간의 팬더 작업을 절약 할 수 있습니다.

7 DuckDB SQL 쿼리는 몇 시간의 팬더 작업을 절약 할 수 있습니다.

DuckDB SQL 쿼리DuckDB SQL 쿼리
저자의 이미지 | 캔버

Pandas Library에는 가장 빠르게 성장하는 커뮤니티 중 하나가 있습니다. 이 인기는 폴라와 같은 대안의 문을 열었습니다. 이 기사에서는 그러한 대안 중 하나 인 DuckDB를 탐색 할 것입니다.

DuckDB는 노트북에서 바로 실행할 수있는 SQL 데이터베이스입니다. 설정이 필요하지 않으며 서버가 필요하지 않습니다. 설치하기 쉽고 팬더와 병렬로 작업 할 수 있습니다.

다른 SQL 데이터베이스와 달리 서버를 구성 할 필요가 없습니다. 설치 후 노트북에서만 작동합니다. 즉, 로컬 설정 두통이 없으며 코드를 즉시 작성합니다. DuckDB는 팬더에 비해 깨끗한 SQL 구문으로 필터링, 조인 및 집계를 처리하며 대형 데이터 세트에서 훨씬 더 잘 수행됩니다.

따라서 용어로 충분히 시작하겠습니다!

데이터 프로젝트 – Uber 비즈니스 모델링

우리는 Jupyter Notebook과 함께 사용하여 데이터 분석을 위해 Python과 결합합니다. 일을 더 흥미롭게 만들기 위해 실제 데이터 프로젝트를 수행 할 것입니다. 시작합시다!

DuckDB SQL 쿼리의 데이터 프로젝트 예제DuckDB SQL 쿼리의 데이터 프로젝트 예제

다음은이 기사에서 사용할 데이터 프로젝트에 대한 링크입니다. 파트너의 비즈니스 모델링이라는 Uber의 데이터 프로젝트입니다.

Uber는 데이터 과학 위치에 대한 채용 프로세스 에서이 데이터 프로젝트를 사용했으며 두 가지 다른 시나리오에 대한 데이터를 분석하라는 요청을받습니다.

  • 시나리오 1 : 바쁜 하루 동안 더 많은 운전자를 온라인으로 확보하기 위해 설계된 두 가지 보너스 프로그램의 비용을 비교하십시오.
  • 시나리오 2 : 전통적인 택시 운전사의 연간 순이익을 계산하고 비교하여 Uber와 파트너와 자동차를 구매하는 사람.

로드 데이터 세트

먼저 데이터 프레임을로드합시다. 이 단계는 필요할 것입니다. 따라서이 데이터 세트를 다음 섹션에서 DUPDB에 등록합니다.

import pandas as pd
df = pd.read_csv("dataset_2.csv")

데이터 세트 탐색

처음 몇 행은 다음과 같습니다.

DuckDB SQL 쿼리의 데이터 프로젝트 예제DuckDB SQL 쿼리의 데이터 프로젝트 예제

모든 열을 보자.

다음은 출력입니다.

DuckDB SQL 쿼리의 데이터 프로젝트 예제DuckDB SQL 쿼리의 데이터 프로젝트 예제

DuckDB를 연결하고 데이터 프레임을 등록하십시오

좋아요, 정말 간단한 데이터 세트이지만이 데이터 세트와 DuckDB를 어떻게 연결할 수 있습니까?
먼저, 아직 설치하지 않은 경우 DuckDB를 설치하십시오.

DuckDB와 연결하는 것은 쉽습니다. 또한 문서를 읽으려면 여기에서 확인하십시오.

이제 데이터 프레임을 연결하고 등록하는 코드가 있습니다.

import duckdb
con = duckdb.connect()

con.register("my_data", df)

DuckDB를 연결하고 데이터 프레임을 등록하십시오DuckDB를 연결하고 데이터 프레임을 등록하십시오

좋아, 몇 시간의 팬더 작업을 절약 할 수있는 7 개의 쿼리를 탐색하자!

1. 복잡한 자격 규칙에 대한 다중 기준 필터링

SQL의 가장 중요한 장점 중 하나는 자연스럽게 필터링, 특히 다중 조건 필터링을 매우 쉽게 처리하는 방법입니다.

DuckDB 대 팬더에서 다중 기준 필터링 구현

DuckDB를 사용하면 필터 수가 증가함에 따라 Clauses and Logic이있는 SQL을 사용하여 여러 필터를 적용 할 수 있습니다.

SELECT 
    *
FROM data
WHERE condition_1
  AND condition_2
  AND condition_3
  AND condition_4

이제 팬더에서 동일한 논리를 어떻게 쓸 것인지 살펴 보겠습니다. 팬더에서는 작은 논리는 괄호가 달린 체인 부울 마스크를 사용하여 표현되며, 이는 많은 조건에서 동점을 얻을 수 있습니다.

filtered_df = df[
    (df["condition_1"]) &
    (df["condition_2"]) &
    (df["condition_3"]) &
    (df["condition_4"])
]

두 방법 모두 똑같이 읽을 수 있으며 기본 사용에 적용 할 수 있습니다. DuckDB는 논리가 더 복잡해지면서 더 자연스럽고 깨끗하다고 ​​느낍니다.

Uber 데이터 프로젝트의 다중 기준 필터링

이 경우 특정 Uber 보너스 프로그램을받을 자격이있는 운전자를 찾고자합니다.

규칙에 따라 운전자는 다음을해야합니다.

  • 최소 8 시간 동안 온라인 상태를 유지하십시오
  • 최소 10 번의 여행을 완료하십시오
  • 승차 요청의 90% 이상을 수락하십시오
  • 등급은 4.7 이상입니다

이제 우리가해야 할 일은이 모든 필터링을 수행하는 쿼리를 작성하는 것입니다. 코드는 다음과 같습니다.

SELECT 
    COUN(*) AS qualified_drivers,
    COUNT(*) * 50 AS total_payout
FROM data
WHERE "Supply Hours" >= 8
  AND CAST(REPLACE("Accept Rate", '%', '') AS DOUBLE) >= 90
  AND "Trips Completed" >= 10
  AND Rating >= 4.7

그러나 Python 으로이 코드를 실행하려면 다음과 같이 Con.Execute ( “” “” “”) 및 FetchDF () 메소드를 추가해야합니다.

con.execute("""
SELECT 
    COUNT(*) AS qualified_drivers,
    COUNT(*) * 50 AS total_payout
FROM data
WHERE "Supply Hours" >= 8
  AND CAST(REPLACE("Accept Rate", '%', '') AS DOUBLE) >= 90
  AND "Trips Completed" >= 10
  AND Rating >= 4.7
""").fetchdf()

우리는 기사 전체에서 이것을 할 것입니다. Jupyter 노트북에서 실행하는 방법을 알았으므로 지금부터 SQL 코드 만 표시하며 Pythonic 버전으로 변환하는 방법을 알게 될 것입니다.
좋은. 이제 데이터 프로젝트는 옵션 1에 대한 총 지불금을 계산하기를 원합니다.

다중 기준 필터링다중 기준 필터링

우리는 드라이버의 합을 계산했지만, 지불금은 각 운전자마다 $ 50가되므로이를 $ 50에 곱해야하므로 Count로 수행합니다.
* 50.

다음은 출력입니다.
다중 기준 필터링

다중 기준 필터링

2. 비즈니스 인센티브를 추정하기위한 빠른 집계

SQL은 특히 행에 대한 데이터를 요약해야 할 때 빠르게 집계하는 데 적합합니다.

DuckDB 대 팬더에서 집계 구현

SELECT 
    COUNT(*) AS num_rows,
    SUM(column_name) AS total_value
FROM data
WHERE some_condition

DuckDB를 사용하면 SUM과 같은 SQL 기능을 사용하여 행의 값을 하나의 소형 블록으로 계산할 수 있습니다.

filtered = df[df["some_condition"]]
num_rows = filtered.shape[0]
total_value = filtered["column_name"].sum()

팬더에서는 먼저 데이터 프레임을 필터링 한 다음 체인링 방법을 사용하여 별도로 계산하고 합계해야합니다.

DuckDB는 더 간결하고 읽기 쉽고 중간 변수를 관리 할 필요가 없습니다.

Uber 데이터 프로젝트의 집계

  • 좋아요
  • 그들은 최소 12 번의 여행을 완료합니다.

등급은 4.7 이상입니다.

SELECT 
    COUNT(*) AS qualified_drivers,
    SUM("Trips Completed") * 4 AS total_payout
FROM data
WHERE "Trips Completed" >= 12
  AND Rating >= 4.7

이번에는 운전자를 계산하는 대신 보너스가 1 인당이 아닌 여행 당 지불되기 때문에 완료 한 여행 수를 추가해야합니다.

여기의 수는 몇 명의 운전자가 자격을 갖춘 수를 알려줍니다. 그러나 총 지불금을 계산하기 위해 옵션 2에서 요구하는대로 여행을 계산하고 4 달러를 곱합니다.
DuckDB의 집계
DuckDB의 집계

다음은 출력입니다.
DuckDB의 집계
DuckDB의 집계

DuckDB를 사용하면 행을 루프하거나 사용자 정의 집계를 구축 할 필요가 없습니다. 합 함수는 우리가 필요한 모든 것을 처리합니다.

3. 부울 논리를 사용하여 겹침과 차이를 감지합니다

SQL에서는 부울 논리를 사용하여 및 OR, OR 및, 그리고 조건을 쉽게 결합 할 수 있습니다.

DuckDB 대 팬더에서 부울 논리 구현

SELECT *
FROM data
WHERE condition_a
  AND condition_b
  AND NOT (condition_c)

DuckDB는 WHERE 절을 사용하고 사용하지 않고 기본적으로 부울 논리를 지원합니다.

filtered = df[
    (df["condition_a"]) &
    (df["condition_b"]) &
    ~(df["condition_c"])
]

팬더는 논리 연산자와 마스크와 괄호와 함께“~”사용을 포함하여 괄호와 함께 조합해야합니다.

둘 다 기능적이지만 DuckDB는 논리에 제외 또는 중첩 조건이 포함될 때에 대해 추론하기가 더 쉽습니다.

Uber 데이터 프로젝트에 대한 부울 논리

이제 우리는 옵션 1과 옵션 2를 계산했는데, 다음은 무엇입니까? 이제 비교를 할 시간입니다. 다음 질문을 기억하십시오.
DuckDB의 부울 논리
DuckDB의 부울 논리

SELECT COUNT(*) AS only_option1
FROM data
WHERE "Supply Hours" >= 8
  AND CAST(REPLACE("Accept Rate", '%', '') AS DOUBLE) >= 90
  AND "Trips Completed" >= 10
  AND Rating >= 4.7
  AND NOT ("Trips Completed" >= 12 AND Rating >= 4.7)

이것은 부울 논리를 사용할 수있는 곳입니다. 우리는 조합을 사용할 것입니다.

다음은 출력입니다.
DuckDB의 부울 논리
DuckDB의 부울 논리

  • 분해합시다 :
  • 처음 네 조건은 옵션 1의 경우 여기에 있습니다.

NOT (..) 부분은 옵션 2에 대한 자격이있는 드라이버를 제외하는 데 사용됩니다.

꽤 간단합니다.

4. 조건부 필터를 사용한 빠른 코호트 크기

때로는 특정 그룹이나 코호트가 데이터 내에 얼마나 큰지 이해하고 싶습니다.

DuckDB 대 팬더에서 조건부 필터 구현?

SELECT 
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM data), 2) AS percentage
FROM data
WHERE condition_1
  AND condition_2
  AND condition_3

DuckDB는 하위 쿼리를 포함하여 하나의 SQL 쿼리로 코호트 필터링 및 백분율 계산을 처리합니다.

filtered = df[
    (df["condition_1"]) &
    (df["condition_2"]) &
    (df["condition_3"])
]
percentage = round(100.0 * len(filtered) / len(df), 2)

팬더는 백분율을 계산하기 위해 필터링, 계산 및 수동 부서가 필요합니다.

여기 Duckdb는 더 깨끗하고 빠릅니다. 단계 수를 최소화하고 반복 코드를 피합니다.

Uber 데이터 프로젝트의 코호트 크기

  • 이제 우리는 시나리오 1의 마지막 질문에 있습니다.이 질문에서 Uber는 여행 및 수락 속도와 같은 일부 작업을 달성 할 수 없지만 등급, 특히 드라이버를 가졌던 운전자를 찾기를 원합니다.
  • 10 회 미만의 여행이 완료되었습니다
  • 90보다 낮은 수용률이있었습니다

4.7보다 높은 등급을 받았습니다

SELECT 
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM data), 2) AS percentage
FROM data
WHERE "Trips Completed" 

이제 이것들은 세 개의 별도 필터이며, 각 필터를 만족시키는 드라이버의 백분율을 계산하려고합니다. 쿼리를 보자.

다음은 출력입니다.
DuckDB의 코호트 크기
DuckDB의 코호트 크기

여기서, 우리는 세 가지 조건이 모두 만족하고 계수 한 행을 필터링하여 총 드라이버 수로 나뉘어 백분율을 얻었습니다.

5. 수익 모델링을위한 기본 산술 쿼리

이제 기본 수학을하고 싶다고 가정 해 봅시다. Select 문에 직접 표현을 쓸 수 있습니다.

DuckDB 대 팬더에서 산술 구현

SELECT 
    daily_income * work_days * weeks_per_year AS annual_revenue,
    weekly_cost * weeks_per_year AS total_cost,
    (daily_income * work_days * weeks_per_year) - (weekly_cost * weeks_per_year) AS net_income
FROM data

DuckDB는 산술이 계산기와 같은 선택 조항에 직접 작성할 수 있도록합니다.

daily_income = 200
weeks_per_year = 49
work_days = 6
weekly_cost = 500

annual_revenue = daily_income * work_days * weeks_per_year
total_cost = weekly_cost * weeks_per_year
net_income = annual_revenue - total_cost

팬더는 동일한 결과를 위해 별도의 변수로 여러 중간 계산이 필요합니다.

DuckDB는 수학 로직을 읽을 수있는 SQL 블록으로 단순화하는 반면, 팬더는 가변 할당과 약간 어지럽습니다.

Uber 데이터 프로젝트의 기본 산술

시나리오 2에서 Uber는 Uber와 파트너 관계없이 운전자가 매년 얼마나 많은 돈을 지불하는지 계산 해달라고 요청했습니다. 가스, 임대료 및 보험과 같은 비용은 다음과 같습니다.
DuckDB의 기본 산술
DuckDB의 기본 산술

SELECT 
    200 * 6 * (52 - 3) AS annual_revenue,
    200 * (52 - 3) AS gas_expense,
    500 * (52 - 3) AS rent_expense,
    400 * 12 AS insurance_expense,
    (200 * 6 * (52 - 3)) 
      - (200 * (52 - 3) + 500 * (52 - 3) + 400 * 12) AS net_income

이제 연간 수익을 계산하고 비용을 빼 봅시다.

다음은 출력입니다.
DuckDB의 기본 산술
DuckDB의 기본 산술

DuckDB를 사용하면 SQL 행렬 블록처럼 쓸 수 있습니다. 팬더 데이터 프레임이나 수동 루핑이 필요하지 않습니다!

6. 동적 비용 계획에 대한 조건부 계산

특정 조건에 따라 비용 구조가 변경되면 어떻게됩니까?

DuckDB 대 팬더에서 조건부 계산 구현

SELECT 
    original_cost * 1.05 AS increased_cost,
    original_cost * 0.8 AS discounted_cost,
    0 AS removed_cost,
    (original_cost * 1.05 + original_cost * 0.8) AS total_new_cost

DuckDB를 사용하면 쿼리 내부의 산술 조정을 사용하여 조건부 로직을 적용 할 수 있습니다.

weeks_worked = 49
gas = 200
insurance = 400

gas_expense = gas * 1.05 * weeks_worked
insurance_expense = insurance * 0.8 * 12
rent_expense = 0
total = gas_expense + insurance_expense

Pandas는 여러 수학 라인과 변수에 대한 수동 업데이트와 동일한 논리를 사용합니다.

DuckDB는 팬더의 다단계 논리를 단일 SQL 표현식으로 바꿉니다.

Uber 데이터 프로젝트의 조건부 계산

  • 이 시나리오에서는 이제 운전자가 Uber와 파트너와 자동차를 구입하면 어떻게되는지 모델링합니다. 비용은 마찬가지입니다
  • 가스 비용 증가 5% 증가
  • 보험은 20% 감소
con.execute("""
SELECT 
    200 * 1.05 * 49 AS gas_expense,
    400 * 0.8 * 12 AS insurance_expense,
    0 AS rent_expense,
    (200 * 1.05 * 49) + (400 * 0.8 * 12) AS total_expense
""").fetchdf()

더 이상 임대료 비용이 없습니다

다음은 출력입니다.
DuckDB의 조건부 계산

DuckDB의 조건부 계산

7. 수익 목표를위한 목표 중심 수학

때로는 수익 목표를 달성하거나 한 번의 비용을 충당하는 것과 같은 비즈니스 목표에 의해 분석을 중단 할 수 있습니다.

DuckDB 대 팬더에서 목표 중심 수학 구현

WITH vars AS (
  SELECT base_income, cost_1, cost_2, target_item
),
calc AS (
  SELECT 
    base_income - (cost_1 + cost_2) AS current_profit,
    cost_1 * 1.1 + cost_2 * 0.8 + target_item AS new_total_expense
  FROM vars
),
final AS (
  SELECT 
    current_profit + new_total_expense AS required_revenue,
    required_revenue / 49 AS required_weekly_income
  FROM calc
)
SELECT required_weekly_income FROM final

DuckDB는 CTE를 사용하여 다단계 로직을 처리합니다. 쿼리를 모듈화하고 쉽게 읽을 수 있습니다.

weeks = 49
original_income = 200 * 6 * weeks
original_cost = (200 + 500) * weeks + 400 * 12
net_income = original_income - original_cost

# new expenses + car cost
new_gas = 200 * 1.05 * weeks
new_insurance = 400 * 0.8 * 12
car_cost = 40000

required_revenue = net_income + new_gas + new_insurance + car_cost
required_weekly_income = required_revenue / weeks

팬더는 중복을 피하기 위해 계산의 중첩 및 이전 변수를 재사용해야합니다.

DuckDB를 사용하면 흩어져있는 코드로 노트북을 어수선하지 않고 로직 파이프 라인을 단계별로 구축 할 수 있습니다.

Uber 데이터 프로젝트의 목표 중심 수학

이제 새로운 비용을 모델링 했으므로 최종 비즈니스 질문에 답하겠습니다.

  • 운전자는 둘 다를 위해 일주일에 얼마나 많은 돈을 벌어야합니까?
  • 1 년 이내에 $ 40.000 자동차를 지불하십시오

같은 연간 순이익을 유지하십시오

WITH vars AS (
  SELECT 
    52 AS total_weeks_per_year,
    3 AS weeks_off,
    6 AS days_per_week,
    200 AS fare_per_day,
    400 AS monthly_insurance,
    200 AS gas_per_week,
    500 AS vehicle_rent,
    40000 AS car_cost
),
base AS (
  SELECT 
    total_weeks_per_year,
    weeks_off,
    days_per_week,
    fare_per_day,
    monthly_insurance,
    gas_per_week,
    vehicle_rent,
    car_cost,
    total_weeks_per_year - weeks_off AS weeks_worked,
    (fare_per_day * days_per_week * (total_weeks_per_year - weeks_off)) AS original_annual_revenue,
    (gas_per_week * (total_weeks_per_year - weeks_off)) AS original_gas,
    (vehicle_rent * (total_weeks_per_year - weeks_off)) AS original_rent,
    (monthly_insurance * 12) AS original_insurance
  FROM vars
),
compare AS (
  SELECT *,
    (original_gas + original_rent + original_insurance) AS original_total_expense,
    (original_annual_revenue - (original_gas + original_rent + original_insurance)) AS original_net_income
  FROM base
),
new_costs AS (
  SELECT *,
    gas_per_week * 1.05 * weeks_worked AS new_gas,
    monthly_insurance * 0.8 * 12 AS new_insurance
  FROM compare
),
final AS (
  SELECT *,
    new_gas + new_insurance + car_cost AS new_total_expense,
    original_net_income + new_gas + new_insurance + car_cost AS required_revenue,
    required_revenue / weeks_worked AS required_weekly_revenue,
    original_annual_revenue / weeks_worked AS original_weekly_revenue
  FROM new_costs
)
SELECT 
  ROUND(required_weekly_revenue, 2) AS required_weekly_revenue,
  ROUND(required_weekly_revenue - original_weekly_revenue, 2) AS weekly_uplift
FROM final

이제이 논리를 나타내는 코드를 작성해 봅시다.

다음은 출력입니다.
DuckDB의 목표 중심 수학

DuckDB의 목표 중심 수학

최종 생각

이 기사에서는 DuckDB와 연결하고 데이터를 분석하는 방법을 살펴 보았습니다. 긴 팬더 기능을 사용하는 대신 SQL 쿼리를 사용했습니다. 우리는 또한 Uber가 데이터 과학자 모집 프로세스에서 요청한 실제 데이터 프로젝트를 사용하여이를 수행했습니다.
분석이 많은 작업을 수행하는 데이터 과학자에게는 팬더에 대한 가볍지 만 강력한 대안입니다. 특히 SQL 로직이 문제에 더 적합한 경우 다음 프로젝트에서 사용해보십시오.

네이트 로시디

데이터 과학자이자 제품 전략입니다. 그는 또한 겸임 교수 교육 분석이며, 데이터 과학자들이 최고 회사의 실제 인터뷰 질문과의 인터뷰를 준비하는 플랫폼 인 Stratascratch의 창립자입니다. Nate는 커리어 시장의 최신 트렌드에 대해 글을 쓰고, 인터뷰 조언을 제공하고, 데이터 과학 프로젝트를 공유하며, 모든 SQL을 다룹니다.

출처 참조

Post Comment

당신은 놓쳤을 수도 있습니다