11.1 Feature Engineering क्या है और SQL क्यों?
Machine Learning model को सिर्फ कच्चा data नहीं चाहिए। उसे “Features” चाहिए।Example:
कच्चा Data: order_id, customer_id, order_date, amount
Feature: last_7_day_spend, avg_order_value, days_since_last_order
ML में 80% टाइम Feature Engineering में जाता है। और 90% company में ये काम SQL से ही होता है क्योंकि data पहले से Data Warehouse में पड़ा होता है।
क्यों SQL?
1.Speed: 100 Cr row Python में 2 घंटे, SQL में 2 मिनट
2.Reproducible: Query save कर दो, रोज same feature बन जाएगा
3.No Data Movement: Data को बाहर निकालने की जरूरत नहीं
11.2 Core SQL Functions for Feature Engineering
ML के लिए हमें 4 तरह के features चाहिए। सब SQL से बनेंगे।
Type 1: Time-based Features – “समय से जुड़े फीचर”हर business में समय सबसे important है।
1. Date Part Extraction
SELECT
order_date,
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
EXTRACT(DAYOFWEEK FROM order_date) AS day_of_week, — 1=Sunday
CASE WHEN EXTRACT(DAYOFWEEK FROM order_date) IN (1,7) THEN 1 ELSE 0 END AS is_weekend
FROM orders;
ML Use: Weekend पर sale ज्यादा होती है। Model ये सीख जाएगा।
2.LAG() and LEAD() – पिछले/आगे वाले row
Customer का behavior समझने के लिए
SELECT
customer_id,
order_date,
amount,
LAG(amount, 1) OVER(PARTITION BY customer_id ORDER BY order_date) AS prev_order_amount,
amount – LAG(amount, 1) OVER(PARTITION BY customer_id ORDER BY order_date) AS amount_change,
LEAD(order_date, 1) OVER(PARTITION BY customer_id ORDER BY order_date) AS next_order_date,
DATEDIFF(LEAD(order_date, 1) OVER(PARTITION BY customer_id ORDER BY order_date), order_date) AS days_to_next_order
FROM orders;
ML Use: amount_change से पता चलेगा customer का spend बढ़ रहा या घट रहा। days_to_next_order से Churn predict करेंगे।
3. Rolling Window – Rolling Average
Stock, Sales, User Activity के लिए
SELECT
sale_date,
daily_revenue,
AVG(daily_revenue) OVER(ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7_day_avg,
SUM(daily_revenue) OVER(ORDER BY sale_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30_day_sum
FROM daily_sales;
ML Use: Model को trend पकड़ने में मदद। एक दिन का spike noise है, 7 दिन का avg trend है।
Type 2: Aggregation Features – “Group करके फीचर”
Customer, Product, City level पर data समेटना।
1. Basic Aggregation
SELECT
customer_id,
COUNT(order_id) AS total_orders, — Frequency
SUM(amount) AS total_spend, — Monetary
AVG(amount) AS avg_order_value,
MIN(order_date) AS first_order_date,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id;
ML Use: यही RFM model की नींव है।
2. Conditional Aggregation – CASE के साथ
SELECT
customer_id,
SUM(CASE WHEN category = ‘Electronics’ THEN amount ELSE 0 END) AS electronics_spend,
SUM(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) AS big_order_count,
COUNT(DISTINCT product_id) AS unique_products_bought
FROM order_items
GROUP BY customer_id;
ML Use: Model को पता चलेगा ये customer “Electronics वाला” है या “Grocery वाला”।
3. Ratio and Percentage Features
SELECT
customer_id,
total_spend,
electronics_spend,
ROUND(electronics_spend * 100.0 / total_spend, 2) AS electronics_spend_pct
FROM customer_features;
ML Use: Absolute number से ज्यादा Ratio important होता है। 1000 में से 800 electronics = Electronics Lover
Type 3: Ranking and Segmentation Features
1. RANK(), DENSE_RANK(), NTILE()
SELECT
product_id,
total_sales,
RANK() OVER(ORDER BY total_sales DESC) AS sales_rank,
NTILE(4) OVER(ORDER BY total_sales DESC) AS quartile — 1=Top 25%
FROM product_sales;
ML Use: quartile=1 वाले product को “Star Product” tag दो। Model को target करने में आसानी।
2. RFM Segmentation – सबसे Powerful Feature
WITH rfm AS (
SELECT
customer_id,
DATEDIFF(CURRENT_DATE, MAX(order_date)) AS recency,
COUNT(order_id) AS frequency,
SUM(amount) AS monetary
FROM orders
GROUP BY customer_id
)
SELECT *,
NTILE(5) OVER(ORDER BY recency ASC) AS r_score, — कम recency = अच्छा
NTILE(5) OVER(ORDER BY frequency DESC) AS f_score,
NTILE(5) OVER(ORDER BY monetary DESC) AS m_score,
CONCAT(r_score, f_score, m_score) AS rfm_segment
FROM rfm;
ML Use: rfm_segment=555 = Best Customer. 111 = Churn होने वाला। Directly model में डाल दो।
Type 4: Text and Categorical Features
1. Count and Frequency Encoding
SELECT
o., c.city_order_count FROM orders o JOIN ( SELECT city, COUNT() AS city_order_count
FROM orders
GROUP BY city
) c ON o.city = c.city;
ML Use: “Mumbai” को 1, “Delhi” को 2 करने से अच्छा है “Mumbai में कितने order हुए” ये बताना।
2. String Parsing
SELECT
email,
SPLIT_PART(email, ‘@’, 2) AS email_domain,
LENGTH(product_name) AS name_length,
CASE WHEN product_name LIKE ‘%Pro%’ THEN 1 ELSE 0 END AS is_pro_version
FROM users;
ML Use: @gmail.com वाले vs @company.com वाले का behavior अलग होता है।
11.3 Real Case Study: E-Commerce Churn Prediction
Problem: कौन सा customer अगले 30 दिन में order नहीं करेगा?
Step 1: Base Table बनाओ
CREATE TABLE customer_features AS
SELECT
customer_id,
— Time Features
DATEDIFF(CURRENT_DATE, MAX(order_date)) AS days_since_last_order,
AVG(DATEDIFF(next_order_date, order_date)) AS avg_days_between_orders,
— Aggregation Features
COUNT(order_id) AS total_orders,
SUM(amount) AS total_spend,
AVG(amount) AS avg_order_value,
— Rolling Features
SUM(amount) FILTER(WHERE order_date >= CURRENT_DATE – INTERVAL ’30 days’) AS last_30_day_spend,
— Ranking Features
RANK() OVER(ORDER BY SUM(amount) DESC) AS spend_rank
FROM (
SELECT *, LEAD(order_date) OVER(PARTITION BY customer_id ORDER BY order_date) AS next_order_date
FROM orders
) t
GROUP BY customer_id;
Step 2: Target Variable बनाओ
SELECT
customer_id,
CASE WHEN days_since_last_order > 30 THEN 1 ELSE 0 END AS is_churned
FROM customer_features;
अब ये table सीधा ML model में डाल दो। Accuracy 85%+ आएगी।
11.4 Big Data के लिए Optimization Tips1 Cr row पर ये query चलानी है तो
1.Partition: PARTITION BY order_date करो। BigQuery/Spark में बहुत fast होगा
2.Index: customer_id, order_date पर index लगाओ
3.Materialized View: Feature table को रोज refresh करो, हर बार calculate मत करो
CREATE MATERIALIZED VIEW mv_customer_features AS
SELECT … FROM orders;
11.5 5 Interview Questions on SQL Feature Engineering
Q1: LAG और Self Join में क्या फर्क है?
A: LAG 1 scan में काम करता है। Self Join 2 बार table scan करता है। LAG 10x fast है।
Q2: Rolling 7 day avg कैसे निकालोगे?
A: AVG() OVER(ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
Q3: Churn कैसे define करोगे?
A: DATEDIFF(now, last_order) > 30
Q4: Categorical को ML के लिए कैसे prepare करोगे?
A: Frequency Encoding या One-Hot via CASE
Q5: Window function और Group By में फर्क?
A: Group By row घटा देता है। Window row नहीं घटाता।…
11.6 Chapter Summary – Cheat Sheet
| Feature Type | Key SQL Functions | ML Use Case |
| Time | EXTRACT, DATEDIFF, LAG, LEAD | Trend, Seasonality, Churn |
| Aggregation | SUM, COUNT, AVG, CASE | RFM, User Behavior |
| Ranking | RANK, NTILE, PERCENT_RANK | Segmentation, Top N |
| Rolling | AVG() OVER(ROWS …) | Smoothing, Trend |
| Text | SPLIT_PART, LENGTH, LIKE | Smoothing, Trend |
Next: Chapter 12: BigQuery + Spark SQL – 100 Cr row को 30 सेकंड में process करना।