اولین پروژه SQL من: تجزیه و تحلیل هزینه های شخصی

ek3nk4r 2024-09-20

0 1 خواندن این مطلب 46 دقیقه زمان میبرد

اولین پروژه SQL من: تجزیه و تحلیل هزینه های شخصی

پیشنهاد ویژه

خرید فالوور واقعی خرید لایک اینستاگرام خرید ویو اینستاگرام خرید فالوور اینستاگرام

Summarize this content to 400 words in Persian Lang
در این پست، من تجربه خود را از تمرین SQL با پاک کردن داده‌های مربوط به هزینه‌های شخصی‌ام، تمیز کردن مجدد داده‌ها (و دوباره) و سپس انجام کمی تجزیه و تحلیل به اشتراک می‌گذارم. من هنوز یک شروع هستم و امیدوارم این مقاله برای افراد تازه کار دیگری مانند خودم جالب باشد.

همچنین! به خاطر نوشتن این پست و حفظ حریم خصوصی، یک کپی “دمو” از هزینه های خود تهیه کردم که شامل مقادیر دلاری تغییر یافته و نام های تجاری است. با این حال، من سعی نکردم هیچ یک از معاملات مرغ سوخاری متعدد را پنهان کنم.

هدف: SQL را با داده های مربوط به زندگی من تمرین کنید

در اوایل امسال، یک دوره مقدماتی SQL را تکمیل کردم که در آن ده ها ساعت را صرف تجزیه و تحلیل پایگاه های داده قدیمی قدیمی مربوط به مسابقات المپیک، نتفلیکس و فیفا کردم. پس از آن، من تشنه ادامه تمرین SQL بودم، اما با مجموعه ای از داده های شخصی تر و مرتبط با زندگی من.

مانند بسیاری از مردم، من به طور معمول برای چیزهایی پول خرج می کنم. سیستم قبلی من برای ردیابی هزینه ها شامل یک برگه مایکروسافت اکسل وحشتناک و زمان زیادی برای عادی سازی داده ها بود. بنابراین تصمیم گرفتم ببینم آیا SQL برای تمیز کردن، خلاصه کردن و دریافت اطلاعات در مورد هزینه های شخصی من بهتر است یا خیر.

به‌علاوه، می‌توانم به برخی از سؤالات واقعاً مهم پاسخ دهم، مانند «کدام رستوران را بیشتر می‌برم؟» و “آیا من به مرغ سوخاری اعتیاد دارم؟”

قسمت 1: طراحی و ساخت میز

اولین قدم بررسی داده های موجود، درک اهدافم و طراحی یک جدول جدول برای حمایت از این اهداف بود.

اطلاعات بانک و کارت اعتباری

چند فایل CSV حاوی تراکنش‌های حساب بانکی و کارت اعتباری در دو سال گذشته را دانلود کردم. این فایل ها حاوی اطلاعات مفیدی در رابطه با هر تراکنش بودند:

تاریخ معامله
مقدار
توضیحات (مثلا TST* رستوران مرغ سرخ شده LLC)
نوع (مثلا اعتبار، بدهی، فروش، بازگشت)
دسته (مثلا خرید، مواد غذایی، سفر کنید) به طور خودکار توسط شرکت کارت اعتباری اختصاص داده می شود. این ستون در فایل‌های حساب بانکی وجود نداشت.

برنامه ریزی طرحواره جدولی

قبل از نوشتن هر کدی، مدتی را صرف فکر کردن به اهدافم و نحوه استفاده از پایگاه داده در آینده کردم:

هدف کوتاه مدت: مخارج را بر اساس دسته بندی، دوره زمانی (به عنوان مثال، سال، سه ماهه، ماه) خلاصه کنید و هزینه های ماه “ثابت” یا “ضروری” را درک کنید.
هدف بلندمدت: اگر این پروژه توسط همسرم (خود یک تحلیلگر تجاری) تایید می‌شد، ممکن است هزینه‌های او را وارد سیستم کنم و بفهمم که چگونه هزینه‌های خانوار را تقسیم می‌کنیم.

از نظر تئوری، این اهداف را می توان با یک جدول محقق کرد. با این حال، برای حفظ انعطاف‌پذیری (و اجتناب از بازنگری انبوه داده‌ها بعداً)، دو جدول اضافه کردم.

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

این transactions جدول سوابق فردی را برای هر تراکنش ورودی و خروجی متعلق به تمام حساب های بانکی و کارت اعتباری ذخیره می کند. جدول دوم accounts اطلاعات اضافی در مورد هر حساب ذخیره می کند (و شاید روزی برای دانستن اینکه کدام هزینه ها متعلق به حساب های متعلق به من یا همسرم است استفاده می شود).

جداول را ایجاد کنید!

با مراجعه به کد زیر می توانید جزئیات بیشتر در مورد جداول و فیلدها را مشاهده کنید. برای مبتدی مثل من، هیجان انگیزترین بخش استفاده از آن بود DECIMAL(10,2) برای amount میدانی که مقادیر دلار و سنت را در خود جای می دهد.

CREATE TABLE accounts (
account_id INTEGER PRIMARY KEY
account_name VARCHAR(255) NOT NULL
account_type VARCHAR(50) NOT NULL
institution VARCHAR(50) NOT NULL
owner VARCHAR(50) NULL
);

CREATE TABLE transactions (
transaction_id SERIAL PRIMARY KEY,
transaction_date DATE NOT NULL,
account_id INTEGER REFERENCES accounts(account_id) NOT NULL
description VARCHAR(255) NOT NULL
category VARCHAR(255) NOT NULL
type VARCHAR(255) NOT NULL
amount DECIMAL(10,2) NOT NULL
);

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

پاک کردن داده ها بدون تغییر داده ها

پس از ایجاد جداول و وارد کردن حدود 1500 تراکنش، یک پرس و جو ساده انجام دادم تا داده ها را بررسی کنم و متوجه مشکلات احتمالی برای رفع آن شوم.

SELECT
transaction_date,
description,
category,
type,
amount
FROM transactions_demo
ORDER BY transaction_date DESC
LIMIT 20;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

همانطور که می بینید، داده ها قبل از اینکه برای تجزیه و تحلیل مفید باشند، نیاز به تمیز کردن و استانداردسازی داشتند. در اینجا چند مسئله وجود دارد:

این description ستون حاوی قالب‌های متناقض، کاراکترهای اضافی (مثلاً رشته‌هایی از متن مربوط به خدمات پردازش پرداخت، شماره‌های صورت‌حساب تصادفی)، و گاهی اوقات همان کسب‌وکار متفاوت ظاهر می‌شود.
بودند NULL ارزش ها در category برای همه تراکنش‌های بانکی، زیرا حساب بانکی به‌طور خودکار دسته‌ای را مانند حساب‌های کارت اعتباری اختصاص نمی‌داد.
همچنین، در حالی که category فیلد عمدتاً صحیح بود، برخی از تراکنش‌ها به دسته‌ای متفاوت از آنچه که توسط کارت اعتباری اختصاص داده شده بود نیاز داشتند. برای مثال، ترجیح می‌دهم هزینه‌های غذای سگ، صورت‌حساب‌های دامپزشکی، و هزینه‌های واکر سگ را به یک سگ جدید اختصاص دهم Dog دسته هزینه
داده ها در type ستون لزوماً اشتباه نبود، اما من احساس کردم که از مقداری تثبیت سود خواهد برد.
تراکنش های زیادی وجود داشت که باید هنگام جمع بندی عادات خرج کردن نادیده گرفته شوند. به عنوان مثال، انتقال بین حساب جاری من و حساب های پس انداز، انتقال به حساب های سرمایه گذاری، پرداخت های کارت اعتباری معمولی.

از آنجایی که من هنوز در حال کاوش در داده ها بودم و مطمئن نبودم که چگونه در آینده از آنها استفاده کنم، می خواستم مشکلات فوق را بدون تغییر خود داده ها برطرف کنم.

ادغام انواع معاملات

انواع تراکنش های پیش فرض خیلی شهودی نبودند، بنابراین تصمیم گرفتم آنها را در سه دسته ادغام کنم.

در اینجا انواع پیش فرض وجود دارد.

SELECT DISTINCT(type) FROM transactions_demo;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

type
———-
CREDIT
Payment
Adjustment
CHECK
DSLIP
Sale
Return
Fee
DEBIT

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

زیر نوشتم CASE بند برای ادغام آنها به سه نوع: In، Out، و Return. از نظر فنی، خریدهای برگشتی را می‌توان با سایر تراکنش‌های دریافتی گروه‌بندی کرد، اما در حال حاضر می‌خواستم به راحتی آنها را جدا کنم – فقط در صورتی که می‌خواستم مثلاً حقوق و درآمدهای دیگر را فیلتر کنم، اما خریدهای برگشتی را حفظ کنم.

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

در زیر نتایج با جدید است clean_type اضافه شده است.

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

رفع دسته های نادرست و NULL

بعدی در لیست تمیز کردن بود NULL ارزش ها و دسته بندی های اشتباه در category زمینه برای درک دسته بندی ها، در اینجا مقادیر متمایز اختصاص داده شده توسط کارت های اعتباری من آمده است.

SELECT DISTINCT(category)
FROM transactions_demo
ORDER BY category;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

category
———————
Automotive
Bills & Utilities
Education
Entertainment
Fees & Adjustments
Food & Drink
Gas
Gifts & Donations
Groceries
Health & Wellness
Home
Personal
Professional Services
Shopping
Travel

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

شناسایی سوابق با NULL مقادیر ساده بود، اما فهمیدن اینکه کدام تراکنش‌ها به دسته‌بندی متفاوتی نیاز دارند، کمی زمان بیشتری گرفت. برای آن سوابق، شناسایی تراکنش‌های تکرارشونده نیاز به توجه را در اولویت قرار دادم. برای این کار از کوئری زیر استفاده کردم.

برخی از نام های فروشنده در description زمینه سازگار نبودند. به عنوان مثال، شرکت خدمات وام مسکن من ظاهر شد ETERNAL HOME LOANS 000… در برخی از نقاط، اما همچنین ظاهر شد ETERNAL HOME LOANS PAYMENTS 000… در بعضی جاها به منظور گروه بندی توضیحات مشابه مانند این، من از آن استفاده کردم LEFT تابعی که فقط 14 کاراکتر اول هر توضیحات را برمی گرداند.

SELECT
COUNT(*),
LEFT(description,14) AS short_description,
category
FROM transactions_demo
WHERE amount < 0
GROUP BY short_description, category
ORDER BY count DESC;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

پس از بررسی، فهرستی از مسائل را تهیه کردم. در اینجا یک زوج وجود دارد:

همانطور که قبلاً ذکر شد، تراکنش های مرتبط با حساب بانکی من همه بودند NULL و به یک دسته بندی جدید نیاز دارد. به عنوان مثال، 24 انتقال به شرکت خدمات رهنی من انجام شد ETERNAL HOME LOANS از جایی که دسته باید تغییر کند NULL به Mortgage.
39 تراکنش به انجام شد COSTCO WHSE اختصاص داده شده است Shopping. من می خواستم اینها را تغییر دهم Groceries از آنجایی که اکثر خریدهای من در Costco غذا و مواد غذایی است.
همچنین قبلاً اشاره شد، معاملات زیادی در رابطه با مالکیت سگ وجود داشت، اما به آنها دسته بندی های مختلفی مانند Personal و Shopping. من می خواستم دسته بندی های اختصاص داده شده به طور خودکار را با یک دسته جدید جایگزین کنم Dog دسته بندی
می خواستم تجمیع کنم Automative و Gas در یک دسته Automative & Gas، بنابراین گروه بندی هزینه های مربوط به مالکیت و رانندگی ماشین من آسان تر خواهد بود.

از آنجایی که تعداد زیادی از فروشندگان و خدمات تکراری وجود نداشت، تصمیم گرفتم که ساده ترین راه حل استفاده از دیگری باشد. CASE بند به عنوان مثال، اگر توضیحات شامل CHEWY.COM، GREYHOUND VETERINARY، یا LOVELY DOGGO WALKO، سپس دسته با جایگزین می شود Dog.

پس از بررسی اغلب توصیفات تکراری، چندین الگو را شناسایی کردم که باید در موارد زیر گنجانده شود CASE بیانیه

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

بعد از اینکه این مورد را از آخرین بار در جستجوی من انداختم …

SELECT
transaction_date,
description,
category,
type,
CASE
WHEN type ~ ‘Adjustment|Return’
THEN ‘Returns & Cashback’
WHEN type ~ ‘CREDIT|DSLIP’
THEN ‘Income & Deposits’
WHEN type ~ ‘CHECK|DEBIT|Fee|Payment|Sale’
THEN ‘Payments & Transfers’
ELSE INITCAP(type) END AS clean_type,
CASE
WHEN description ~ ‘DRIVER LICENSE RENEW|VEHICLE LICENSING|INSUROTRON|PARKING|SPOTHERO|PAYBYPHONE’ OR category ~ ‘Gas|Automotive’
THEN ‘Automotive & Gas’
WHEN description ~ ‘^PAYMENT TO EMPIRE|Credit Card Payment’
THEN ‘Credit card’
WHEN description ~ ‘^REDEMPTION CREDIT’
THEN ‘Points cashback’
WHEN description ~ ‘LOVELY DOGGO|GREYHOUND VETERINARY|LOCAL HOUND GROOMER|CHEWY.COM|WHO LET THE PETS OUT’
THEN ‘Dog’
WHEN description ~ ‘PLAYSTATION|NBA League|NETFLIX|YouTube|NVIDIA|APPLE.COM/BILL|GOOGLE \*Peacock’
THEN ‘Entertainment’
WHEN description ~ ‘COSTCO WHSE|INSTACART’
THEN ‘Groceries’
WHEN description ~ ‘TRVL CARD BANK|PARKSRESERVATIONSWEB|TRUSTEDTRAVELER’
THEN ‘Travel’
WHEN description ~ ‘ATM WITHDRAWAL|FOREIGN EXCHANGE RATE|NON-EMPIRE BANK ATM|^CHECK #’
THEN ‘Misc’
WHEN description ~ ‘^ETERNAL HOME LOANS’
THEN ‘Mortgage’
WHEN description ~ ‘^DEPOSIT ID NUMBER|^IRS.*TAX REF’
THEN ‘Other income’
WHEN description ~ ‘^CORPO INC ACH PMT|^VENMO|^Zelle|^REMOTE ONLINE DEPOSIT’
THEN ‘Reimbursement’
WHEN description ~ ‘^WALLSTREET BROS|^INVESTCORP|ROBBER BARON INVESTM|APA.*TREAS|^Online Transfer’
THEN ‘Saving & Investing’
WHEN description ~ ‘^CORPO INC PAYROLL’
THEN ‘Wages & Salaries’
ELSE category END AS clean_category,
amount
FROM transactions_demo
WHERE transaction_date <= ‘2024-08-06’
AND description !~ ‘NEW TECH NORTHWEST’
ORDER BY transaction_date DESC
LIMIT 20;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

… من مثال زیر را دریافت کردم clean_category ستون

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

حذف هرج و مرج از توضیحات

این description فیلد شامل یک یادداشت در مورد هر تراکنش است. برای مثال، می‌تواند فروشنده‌ای باشد که از آن چیزی خریدم یا نام حساب پس‌اندازی که به آن پول فرستادم.

توضیحات دارای تناقضات قالب بندی متعددی بود و شامل کاراکترهای اضافی بود که گروه بندی آنها را با هم دشوار می کرد. به عنوان مثال، هر تراکنش مربوط به Google Fi (سرویس تلفن من) شامل یک کد فاکتور منحصر به فرد بود. به زیر نگاهی بیندازید.

SELECT
transaction_date,
description
FROM transactions_demo
WHERE description ~* ‘^Google.+Fi’
ORDER BY transaction_date DESC
LIMIT 25;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

اگر بخواهم به سؤالی پاسخ دهم که «سال گذشته چقدر برای خدمات تلفنی خرج کردم»، شرح را به چیزی شبیه به استاندارد کردن Google Fi گروه بندی تراکنش ها را آسان تر می کند.

در مرحله بعد، این عمدتا یک مشکل زیبایی شناختی است، اما نگاهی به معاملات غذاخوری و بیرون‌آوری بیندازید.

SELECT
description,
category
FROM transactions_demo
WHERE category = ‘Food & Drink’
ORDER BY transaction_date DESC
LIMIT 20;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

رستوران ها و بسیاری از مشاغل دیگر از خدمات پردازش پرداخت مانند Square یا Toast استفاده می کنند که یک رشته کوتاه مانند درج می کند SQ * و TST* در ابتدای هر توضیح من می خواستم این رشته های اضافی (و سایر کاراکترها) را حذف کنم تا خوانایی داده ها را افزایش دهم. مثل این:

SQ *RED FISH BLUE FISH –> RED FISH BLUE FISH

SQ *CRAFT (VICTORIA) LTD. –> CRAFT VICTORIA

TST*LUCKY BREWING – CAP –> LUCKY BREWING – CAP

سپس توضیحاتی را که شامل چند کاراکتر خاص بود بررسی کردم:

SELECT
description,
category
FROM transactions_demo
WHERE description ~ ‘\*|\#|\-‘
ORDER BY transaction_date DESC
LIMIT 25;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

بر اساس آن پرس و جو، من می خواستم کاراکترهای اضافی (و اعداد) مانند این را حذف کنم:

COSTCO WHSE #0000 –> COSTCO WHSE

COSTCO GAS #0000 –> COSTCO GAS

LOWES #00000* –> LOWES

و کاراکترهای خاص مانند علامت در این توضیحات را اصلاح کنید:

در نهایت، تراکنش های زیادی وجود داشت که شامل ترکیبی از حروف، اعداد و کاراکترهای خاص در انتهای رشته بود. به آشفتگی مطلق زیر نگاه کنید.

SELECT description
FROM transactions_demo
WHERE description ~* ‘\d+’
ORDER BY transaction_date DESC
LIMIT 25;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

description
————————————————————–
ROBBER BARON INVESTMENTS PPD ID: 0000000000
ETERNAL HOME LOANS 0000000000 WEB ID: 0000000000
PAYMENT TO EMPIRE BANK CARD ENDING IN 0000 00/00
WALLSTREET BROS SAVINGS: 0000000000
TRVL CARD BANK ONLINE PMT CKF000000000POS WEB ID: 0000000000
APA TREAS 000 MISC PAY PPD ID: 0000000000
APA TREAS 000 MISC PAY PPD ID: 0000000000
…
…

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

در این مواقع می خواستم چیزی شبیه به این را تغییر دهم ETERNAL HOME LOANS 0000000000 WEB ID: 0000000000 فقط ETERNAL HOME LOANS.

عبارات منظم برای نجات

با کاوش در ستون توضیحات و بررسی رکوردهای متداول، چندین الگو را شناسایی کردم که باید حذف یا اصلاح شوند. از اونجا یه سری تو در تو نوشتم REPLACE و REGEXP_REPLACE بندهایی که توضیحات را پاک می کند.

— Change first letter to uppercase, rest of string lower case
TRIM(INITCAP(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
REPLACE(description,””,”),
— Fix ampersands (&)
‘&’, ‘&’),
— Replace strings of letters and numbers after a special character (as long as it appears after the eighth char)
‘(?\W\d+.+$’,’ ”g’),
— Replace certain patterns (e.g., ‘TST*’), special characters, unwanted strings(e.g. ‘LLC’)
‘^SP\s|^[a-zA-Z]{2,3}\*|^[a-zA-Z]{2,3} \*|\s[a-zA-Z]{1,3}\d+.+$|\s[a-zA-Z]{3}\s[a-zA-Z]{2}\:\s.|\d|[^\w\s&.]|\sLLC$|\sINC$|\sLTD$|\sLLC\.$|\sINC\.$|\sInc\.$|\sLTD\.$’, ‘ ‘, ‘g’),
— Replace single characters hanging at the end of a string (e.g. ‘Veterinary H’ –> ‘Veterinary’)
‘\s[a-zA-Z]$|\s[a-zA-Z]\s+$’,”, ‘g’),
— Change multiple spaces into one space
‘\s+’, ‘ ‘, ‘g’)
)) AS clean_description

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

من فرض می‌کنم فردی با تجربه بیشتر می‌تواند نسخه مختصرتری بنویسد، اما فکر کردم که برای من به اندازه کافی خوب است. بیایید نگاهی بیندازیم …

SELECT
transaction_date,
description,
TRIM(INITCAP(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
REPLACE(description,””,”),
‘&’, ‘&’),
‘(?\W\d+.+$’,’ ”g’),
‘^SP\s|^[a-zA-Z]{2,3}\*|^[a-zA-Z]{2,3} \*|\s[a-zA-Z]{1,3}\d+.+$|\s[a-zA-Z]{3}\s[a-zA-Z]{2}\:\s.|\d|[^\w\s&.]|\sLLC$|\sINC$|\sLTD$|\sLLC\.$|\sINC\.$|\sInc\.$|\sLTD\.$’, ‘ ‘, ‘g’),
‘\s[a-zA-Z]$|\s[a-zA-Z]\s+$’,”, ‘g’),
‘\s+’, ‘ ‘, ‘g’)
)) AS clean_description
FROM transactions_demo
ORDER BY transaction_date DESC
LIMIT 45;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

…و کار خیلی خوبی انجام داد!!!!

با این حال، کامل نبود. به عنوان مثال، بیایید به خرید از آمازون نگاه کنیم.

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

برای فروشندگان و خدمات مکرر، تصمیم گرفتم سریعتر باشد که برخی از قوانین تخصصی را به آن اضافه کنم CASE بیانیه زمانی که قبلاً فیلد دسته بندی را اصلاح می کردم، قبلاً لیستی از فروشندگان و الگوهای تکراری در description زمینه، بنابراین ایجاد بند دیگری مانند زیر بسیار آسان بود.

TRIM(CASE
WHEN description ~ ‘PAYBYPHONE’
THEN ‘PayByPhone Parking’
WHEN description ~* ‘GOOGLE.*FI’
THEN ‘Google Fi’
WHEN description ~ ‘LOVELY DOGGO WALKO’
THEN ‘Lovely Doggo Walko’
WHEN description ~* ‘PLAYSTATION’
THEN ‘PlayStation Network’
WHEN description ~ ‘SOUTHERN CHICKEN AND WAFF’
THEN ‘Southern Chicken And Waffle’
WHEN description ~ ‘NEIGHBORS GROCERY OUT’
THEN ‘Neighbors Grocery Outlet’
WHEN description ~ ‘AmazonStores|Amazon Fresh’
THEN ‘Amazon Fresh’
WHEN description ~ ‘WALLSTREET BROS’
THEN ‘Wallstreet Bros Savings’
WHEN description ~* ‘AMZN Mktp|Amazon.com|AMAZON MKTPL’
THEN ‘Amazon.com’
WHEN description ~ ‘ATM WITHDRAWAL’
THEN ‘ATM Withdrawal’
WHEN description ~ ‘ETERNAL HOME LOANS’
THEN ‘Eternal Home Loans’
WHEN description ~ ‘AIRBNB’
THEN ‘Airbnb’
WHEN description ~ ‘LYFT.*RIDE’
THEN ‘Lyft Ride’
WHEN description ~* ‘Uniqlo’
THEN ‘Uniqlo’
ELSE INITCAP(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
REPLACE(description,””,”),
‘&’, ‘&’),
‘(?\W\d+.+$’,’ ”g’),
‘^SP\s|^[a-zA-Z]{2,3}\*|^[a-zA-Z]{2,3} \*|\s[a-zA-Z]{1,3}\d+.+$|\s[a-zA-Z]{3}\s[a-zA-Z]{2}\:\s.|\d|[^\w\s&.]|\sLLC$|\sINC$|\sLTD$|\sLLC\.$|\sINC\.$|\sInc\.$|\sLTD\.$’, ‘ ‘, ‘g’),
‘\s[a-zA-Z]$|\s[a-zA-Z]\s+$’,”, ‘g’),
‘\s+’, ‘ ‘, ‘g’)
) END) AS clean_description

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

پرس و جو را به یک عبارت جدول مشترک منتقل کنید

در این مرحله، جدید clean_description، clean_category، و clean_type ستون‌ها بیشتر داده‌های ناسازگار و گمشده را ثابت کردند. وقت آن بود که شروع به جمع بندی و تجزیه و تحلیل هزینه هایم کنم! اما ابتدا … اجازه دهید یک مرحله مهم دیگر را بررسی کنیم.

وقتی سعی کردم تراکنش ها را با استفاده از a فیلتر کنم WHERE بند با clean_category همانطور که در زیر مشاهده می کنید …

SELECT
transaction_date,
description,
CASE
WHEN description ~ ‘DRIVER LICENSE RENEW|VEHICLE LICENSING|INSUROTRON|PARKING|SPOTHERO|PAYBYPHONE’ OR category ~ ‘Gas|Automotive’
THEN ‘Automotive & Gas’
WHEN description ~ ‘^PAYMENT TO EMPIRE|Credit Card Payment’
THEN ‘Credit card’
WHEN description ~ ‘^REDEMPTION CREDIT’
THEN ‘Points cashback’
WHEN description ~ ‘LOVELY DOGGO|GREYHOUND VETERINARY|LOCAL HOUND GROOMER|CHEWY.COM|WHO LET THE PETS OUT’
THEN ‘Dog’
WHEN description ~ ‘PLAYSTATION|NBA League|NETFLIX|YouTube|NVIDIA|APPLE.COM/BILL|GOOGLE \*Peacock’
THEN ‘Entertainment’
WHEN description ~ ‘PHO SUP SHOP|BLING BLING ASIAN MARKET’
THEN ‘Food & Drink’
WHEN description ~ ‘COSTCO WHSE|INSTACART’
THEN ‘Groceries’
WHEN description ~ ‘TRVL CARD BANK|PARKSRESERVATIONSWEB|TRUSTEDTRAVELER’
THEN ‘Travel’
WHEN description ~ ‘ATM WITHDRAWAL|FOREIGN EXCHANGE RATE|NON-EMPIRE BANK ATM|^CHECK #’
THEN ‘Misc’
WHEN description ~ ‘^ETERNAL HOME LOANS’
THEN ‘Mortgage’
WHEN description ~ ‘^DEPOSIT ID NUMBER|^IRS.*TAX REF’
THEN ‘Other income’
WHEN description ~ ‘^CORPO INC ACH PMT|^VENMO|^Zelle|^REMOTE ONLINE DEPOSIT’
THEN ‘Reimbursement’
WHEN description ~ ‘^WALLSTREET BROS|^INVESTCORP|ROBBER BARON INVESTM|APA.*TREAS|^Online Transfer’
THEN ‘Saving & Investing’
WHEN description ~ ‘^CORPO INC PAYROLL’
THEN ‘Wages & Salaries’
ELSE category END AS clean_category,
amount
FROM transactions_demo
— Only show results from the Food & Drink category
WHERE clean_category = ‘Food & Drink’

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

…من با این خطا مواجه شدم.

ERROR: column “clean_category” does not exist
LINE 98: WHERE clean_category = ‘Food & Drink’
^

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

چرا؟ خوب، هنگام حل یک پرس و جو، SQL محتوای موجود در آن را بررسی می کند WHERE بند قبل از حل و فصل SELECT بخشی از عبارت (منطقی است زیرا ما فقط داده هایی را می خواهیم که با آن مطابقت داشته باشند WHERE بند مورد پردازش و بازگرداندن). از آنجایی که CASE بند به همراه بقیه موارد پردازش می شود SELECT بخش، آن زمان وجود ندارد WHERE حل می شود. پس من clean_category زمانی که ستون وجود نداشت WHERE بند حل شد

برای رفع این مشکل، پرس و جو را به یک CTE (عبارت جدول رایج) منتقل کردم. به این ترتیب، ستون‌های «تمیز» من در چیزی شبیه به یک جدول موقت ذخیره می‌شوند و من می‌توانم به داده‌های پاک‌شده هنگام نوشتن یک اشاره کنم. WHERE بند در اینجا نمونه ای از پرس و جو پس از انتقال آن به یک CTE است.

WITH
— Common table expression (CTE) containing my cleaned data
clean_transactions AS (
SELECT
transaction_id,
transaction_date,
— Replace common descriptions and remove extra characters and symbols
TRIM(CASE
WHEN description ~ ‘PAYBYPHONE’
THEN ‘PayByPhone Parking’
WHEN description ~* ‘GOOGLE.*FI’
THEN ‘Google Fi’
WHEN description ~ ‘LOVELY DOGGO WALKO’
THEN ‘Lovely Doggo Walko’
WHEN description ~* ‘PLAYSTATION’
THEN ‘PlayStation Network’
WHEN description ~ ‘SOUTHERN CHICKEN AND WAFF’
THEN ‘Southern Chicken And Waffle’
WHEN description ~ ‘NEIGHBORS GROCERY OUT’
THEN ‘Neighbors Grocery Outlet’
WHEN description ~ ‘AmazonStores|Amazon Fresh’
THEN ‘Amazon Fresh’
WHEN description ~ ‘WALLSTREET BROS’
THEN ‘Wallstreet Bros Savings’
WHEN description ~* ‘AMZN Mktp|Amazon.com|AMAZON MKTPL’
THEN ‘Amazon.com’
WHEN description ~ ‘ATM WITHDRAWAL’
THEN ‘ATM Withdrawal’
WHEN description ~ ‘ETERNAL HOME LOANS’
THEN ‘Eternal Home Loans’
WHEN description ~ ‘AIRBNB’
THEN ‘Airbnb’
WHEN description ~ ‘LYFT.*RIDE’
THEN ‘Lyft Ride’
WHEN description ~* ‘Uniqlo’
THEN ‘Uniqlo’
ELSE INITCAP(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REPLACE(
REPLACE(description,””,”),
‘&’, ‘&’),
— Replace strings of letters and numbers after a special character (as long as it appears after the eighth char)
‘(?\W\d+.+$’,’ ”g’),
— Replace certain patterns (e.g., ‘TST*’), special characters, unwanted strings(e.g. ‘LLC’)
‘^SP\s|^[a-zA-Z]{2,3}\*|^[a-zA-Z]{2,3} \*|\s[a-zA-Z]{1,3}\d+.+$|\s[a-zA-Z]{3}\s[a-zA-Z]{2}\:\s.|\d|[^\w\s&.]|\sLLC$|\sINC$|\sLTD$|\sLLC\.$|\sINC\.$|\sInc\.$|\sLTD\.$’, ‘ ‘, ‘g’),
— Replace single characters hanging at the end of a string (e.g. ‘Veterinary H’ –> ‘Veterinary’)
‘\s[a-zA-Z]$|\s[a-zA-Z]\s+$’,”, ‘g’),
— Change multiple spaces into one space
‘\s+’, ‘ ‘, ‘g’)
) END) AS clean_description,
— Fix wrong categories and add new ones (e.g., Dog, Mortgage, Wages & Salary)
CASE
WHEN description ~ ‘DRIVER LICENSE RENEW|VEHICLE LICENSING|INSUROTRON|PARKING|SPOTHERO|PAYBYPHONE’ OR category ~ ‘Gas|Automotive’
THEN ‘Automotive & Gas’
WHEN description ~ ‘^PAYMENT TO EMPIRE|Credit Card Payment’
THEN ‘Credit card’
WHEN description ~ ‘^REDEMPTION CREDIT’
THEN ‘Points cashback’
WHEN description ~ ‘LOVELY DOGGO|GREYHOUND VETERINARY|LOCAL HOUND GROOMER|CHEWY.COM|WHO LET THE PETS OUT’
THEN ‘Dog’
WHEN description ~ ‘PLAYSTATION|NBA League|NETFLIX|YouTube|NVIDIA|APPLE.COM/BILL|GOOGLE \*Peacock’
THEN ‘Entertainment’
WHEN description ~ ‘COSTCO WHSE|INSTACART’
THEN ‘Groceries’
WHEN description ~ ‘TRVL CARD BANK|PARKSRESERVATIONSWEB|TRUSTEDTRAVELER’
THEN ‘Travel’
WHEN description ~ ‘ATM WITHDRAWAL|FOREIGN EXCHANGE RATE|NON-EMPIRE BANK ATM|^CHECK #’
THEN ‘Misc’
WHEN description ~ ‘^ETERNAL HOME LOANS’
THEN ‘Mortgage’
WHEN description ~ ‘^DEPOSIT ID NUMBER|^IRS.*TAX REF’
THEN ‘Other income’
WHEN description ~ ‘^CORPO INC ACH PMT|^VENMO|^Zelle|^REMOTE ONLINE DEPOSIT’
THEN ‘Reimbursement’
WHEN description ~ ‘^WALLSTREET BROS|^INVESTCORP|ROBBER BARON INVESTM|APA.*TREAS|^Online Transfer’
THEN ‘Saving & Investing’
WHEN description ~ ‘^CORPO INC PAYROLL’
THEN ‘Wages & Salaries’
ELSE category END AS clean_category,
— Group transaction type to simplify grouping later
CASE
WHEN type ~ ‘Adjustment|Return’
THEN ‘Returns & Cashback’
WHEN type ~ ‘CREDIT|DSLIP’
THEN ‘Income & Deposits’
WHEN type ~ ‘CHECK|DEBIT|Fee|Payment|Sale’
THEN ‘Payments & Transfers’
ELSE INITCAP(type) END AS clean_type,
amount,
— Add year, quarter, and month for summarizing transactions later
EXTRACT(YEAR FROM transaction_date) AS year,
TO_CHAR(transaction_date,’YYYY”_Q”Q’) as qtr_string,
TO_CHAR(transaction_date,’YYYY_MM’) as month,
account_id
FROM transactions_demo
)
— Query referencing the new ‘clean_transactions’ CTE
SELECT
transaction_date,
clean_description,
amount
FROM clean_transactions
WHERE clean_category = ‘Food & Drink’
ORDER BY transaction_date DESC;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

transaction_date | clean_description | amount
—————- | ——————— | ——-
2024-08-21 | Buzz N Woody Pizza | -50.33
2024-08-17 | Lucky Brewing Cap | -7.42
2024-08-17 | Mesopotamia Mix | -29.06
2024-08-13 | Hole In Wall Thai | -52.50
2024-08-12 | Rocket Taco Town | -33.59
2024-08-11 | Coast Victoria Hote & | -42.62
2024-08-10 | Cafe Brio | -121.86
2024-08-09 | Friends & Family Bake | -7.90
2024-08-09 | Red Fish Blue Fish | -32.86
2024-08-09 | Irish Times Pub | -74.75
2024-08-09 | Craft Victoria | -31.78
2024-08-03 | Din Tai Fung Belle | -40.10
2024-07-26 | Taqueria El | -24.89
… | … | …

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

بله!!! با clean_transactions CTE، من می توانم در نهایت خلاصه و تجزیه و تحلیل معاملات را شروع کنم.

چند پرس و جو سریع

برای شروع، اجازه دهید کل هزینه های هر دسته را به همراه درصد کل هزینه های آن سال بررسی کنیم.

WITH
…
…
…
SELECT
year,
clean_category,
SUM(amount) AS total_spend,
ROUND(SUM(amount) / (SUM(SUM(amount)) OVER(PARTITION BY year))*100,2) AS pct_of_spend
FROM clean_transactions
WHERE clean_category NOT IN (‘Wages & Salaries’,’Credit card’,’Saving & Investing’,’Other income’,’Points cashback’)
GROUP BY year, clean_category
ORDER BY year DESC, total_spend;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

year | clean_category | total_spend | pct_of_spend
—- | ——————— | ———– | ————
2024 | Mortgage | -16196.71 | 38.78
2024 | Shopping | -5309.93 | 12.71
2024 | Travel | -4350.83 | 10.42
2024 | Dog | -3255.04 | 7.79
2024 | Bills & Utilities | -3208.90 | 7.68
2024 | Food & Drink | -3021.83 | 7.24
2024 | Groceries | -1830.94 | 4.38
2024 | Automotive & Gas | -1446.29 | 3.46
2024 | Misc | -760.00 | 1.82
2024 | Home | -715.45 | 1.71
2024 | Entertainment | -687.91 | 1.65
2024 | Personal | -543.92 | 1.30
2024 | Professional Services | -393.02 | 0.94
2024 | Health & Wellness | -170.78 | 0.41
2024 | Gifts & Donations | -76.00 | 0.18
… | … | … | …

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

یا فقط برای سرگرمی، می‌توانم پربازدیدترین رستوران‌هایم را در سال 2024 بررسی کنم.

WITH
…
…
…
SELECT
COUNT(clean_description) AS visits,
clean_description AS restaurant
FROM clean_transactions
WHERE clean_category IN(‘Food & Drink’)
AND year = ‘2024’
GROUP BY clean_description
ORDER BY visits DESC;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

…بنابراین، بله، ممکن است مشکل مرغ سوخاری در این خانواده وجود داشته باشد.

با تشکر از شما برای خواندن!

امیدوارم تجربه من ایده هایی را برای پروژه های کدنویسی شخصی یا ردیابی هزینه شما ارائه کرده باشد. در پست بعدی، هزینه‌های “ضروری” و “غیر ضروری” خود را بررسی می‌کنم، خریدهای بزرگی را پیدا می‌کنم که از میانگین مبلغ تراکنش بیشتر است و هزینه‌های سه ماهه را خلاصه می‌کنم. در ضمن، اگر سوال یا پیشنهادی دارید، لطفاً با من در میان بگذارید.

اگر تا اینجا پیش رفتید، من یک سوال از شما دارم: “وقتی برای اولین بار شروع به یادگیری SQL کردید، چه نوع پروژه های شخصی انجام دادید؟”

در این پست، من تجربه خود را از تمرین SQL با پاک کردن داده‌های مربوط به هزینه‌های شخصی‌ام، تمیز کردن مجدد داده‌ها (و دوباره) و سپس انجام کمی تجزیه و تحلیل به اشتراک می‌گذارم. من هنوز یک شروع هستم و امیدوارم این مقاله برای افراد تازه کار دیگری مانند خودم جالب باشد.

فهرست مطالب

هدف: SQL را با داده های مربوط به زندگی من تمرین کنید

قسمت 1: طراحی و ساخت میز

اولین قدم بررسی داده های موجود، درک اهدافم و طراحی یک جدول جدول برای حمایت از این اهداف بود.

اطلاعات بانک و کارت اعتباری

تاریخ معامله
مقدار
توضیحات (مثلا TST* رستوران مرغ سرخ شده LLC)
نوع (مثلا اعتبار، بدهی، فروش، بازگشت)
دسته (مثلا خرید، مواد غذایی، سفر کنید) به طور خودکار توسط شرکت کارت اعتباری اختصاص داده می شود. این ستون در فایل‌های حساب بانکی وجود نداشت.

برنامه ریزی طرحواره جدولی

قبل از نوشتن هر کدی، مدتی را صرف فکر کردن به اهدافم و نحوه استفاده از پایگاه داده در آینده کردم:

هدف کوتاه مدت: مخارج را بر اساس دسته بندی، دوره زمانی (به عنوان مثال، سال، سه ماهه، ماه) خلاصه کنید و هزینه های ماه “ثابت” یا “ضروری” را درک کنید.
هدف بلندمدت: اگر این پروژه توسط همسرم (خود یک تحلیلگر تجاری) تایید می‌شد، ممکن است هزینه‌های او را وارد سیستم کنم و بفهمم که چگونه هزینه‌های خانوار را تقسیم می‌کنیم.

 --------------------------------         ----------------------------
| transactions     |             |       | accounts     |             |
| ---------------- | ----------- |       | ------------ | ----------- |
| transaction_id   | primary key |   |‾‾‾| account_id   | primary key | 
| transaction_date |             |   |   | account_name |             |
| account_id       | foreign key |‾‾‾    | account_type |             |
| description      |             |       | institution  |             |
| category         |             |       | owner        |             |
| type             |             |        ----------------------------
| amount           |             |      
 --------------------------------

این transactions جدول سوابق فردی را برای هر تراکنش ورودی و خروجی متعلق به تمام حساب های بانکی و کارت اعتباری ذخیره می کند. جدول دوم accounts اطلاعات اضافی در مورد هر حساب ذخیره می کند (و شاید روزی برای دانستن اینکه کدام هزینه ها متعلق به حساب های متعلق به من یا همسرم است استفاده می شود).

جداول را ایجاد کنید!

با مراجعه به کد زیر می توانید جزئیات بیشتر در مورد جداول و فیلدها را مشاهده کنید. برای مبتدی مثل من، هیجان انگیزترین بخش استفاده از آن بود DECIMAL(10,2) برای amount میدانی که مقادیر دلار و سنت را در خود جای می دهد.

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY
    account_name VARCHAR(255) NOT NULL
    account_type VARCHAR(50) NOT NULL
    institution VARCHAR(50) NOT NULL
    owner VARCHAR(50) NULL
);

CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    account_id INTEGER REFERENCES accounts(account_id) NOT NULL
    description VARCHAR(255) NOT NULL
    category VARCHAR(255) NOT NULL
    type VARCHAR(255) NOT NULL
    amount DECIMAL(10,2) NOT NULL
);

پاک کردن داده ها بدون تغییر داده ها

SELECT
    transaction_date,
    description,
    category,
    type,
    amount
FROM transactions_demo
ORDER BY transaction_date DESC
LIMIT 20;

transaction_date | description                                | category          | type    |   amount   
---------------- | ------------------------------------------ | ----------------- | ------- | -------- 
2024-07-31       | Uniqlo USA LLC                             | Shopping          | Sale    |  -101.08 
2024-07-31       | ETERNAL HOME LOANS   0000000000  WEB ID... | NULL              | DEBIT   | -2348.33 
2024-07-31       | GOOGLE *FI DH0TCM                          | Bills & Utilities | Sale    |  -103.39 
2024-07-30       | Credit Card Payment - Thank You            | NULL              | Payment |  1972.01 
2024-07-30       | PAYMENT TO EMPIRE BANK CARD ENDING IN 0... | NULL              | DEBIT   | -1972.01 
2024-07-30       | TTP* LOVELY DOGGO WALKO                    | Personal          | Sale    |   -20.80 
2024-07-29       | GOOGLE *Peacock                            | Shopping          | Sale    |   -12.35 
2024-07-29       | NEIGHBORS GROCERY OUT                      | Groceries         | Sale    |   -31.57 
2024-07-29       | AMC 0000 ONLINE                            | Entertainment     | Sale    |   -39.78 
2024-07-26       | TAQUERIA EL                                | Food & Drink      | Sale    |   -24.89 
2024-07-25       | SQ *JACK STRANDS SALON                     | Personal          | Sale    |   -56.64 
2024-07-25       | GREYHOUND VETERINARY H                     | Personal          | Sale    |   -87.19 
2024-07-24       | B&H PHOTO 000-000-0000                 | Shopping          | Sale    |   -70.58 
2024-07-24       | EVIL INTERNET & CABLE CORP                 | Bills & Utilities | Sale    |   -77.25 
...              | ...                                        | ...               | ...     |      ...

این description ستون حاوی قالب‌های متناقض، کاراکترهای اضافی (مثلاً رشته‌هایی از متن مربوط به خدمات پردازش پرداخت، شماره‌های صورت‌حساب تصادفی)، و گاهی اوقات همان کسب‌وکار متفاوت ظاهر می‌شود.
بودند NULL ارزش ها در category برای همه تراکنش‌های بانکی، زیرا حساب بانکی به‌طور خودکار دسته‌ای را مانند حساب‌های کارت اعتباری اختصاص نمی‌داد.
همچنین، در حالی که category فیلد عمدتاً صحیح بود، برخی از تراکنش‌ها به دسته‌ای متفاوت از آنچه که توسط کارت اعتباری اختصاص داده شده بود نیاز داشتند. برای مثال، ترجیح می‌دهم هزینه‌های غذای سگ، صورت‌حساب‌های دامپزشکی، و هزینه‌های واکر سگ را به یک سگ جدید اختصاص دهم Dog دسته هزینه
داده ها در type ستون لزوماً اشتباه نبود، اما من احساس کردم که از مقداری تثبیت سود خواهد برد.
تراکنش های زیادی وجود داشت که باید هنگام جمع بندی عادات خرج کردن نادیده گرفته شوند. به عنوان مثال، انتقال بین حساب جاری من و حساب های پس انداز، انتقال به حساب های سرمایه گذاری، پرداخت های کارت اعتباری معمولی.

ادغام انواع معاملات

انواع تراکنش های پیش فرض خیلی شهودی نبودند، بنابراین تصمیم گرفتم آنها را در سه دسته ادغام کنم.

در اینجا انواع پیش فرض وجود دارد.

SELECT DISTINCT(type) FROM transactions_demo;

type
----------
CREDIT  
Payment     
Adjustment  
CHECK           
DSLIP
Sale
Return
Fee
DEBIT

زیر نوشتم CASE بند برای ادغام آنها به سه نوع: In، Out، و Return. از نظر فنی، خریدهای برگشتی را می‌توان با سایر تراکنش‌های دریافتی گروه‌بندی کرد، اما در حال حاضر می‌خواستم به راحتی آنها را جدا کنم – فقط در صورتی که می‌خواستم مثلاً حقوق و درآمدهای دیگر را فیلتر کنم، اما خریدهای برگشتی را حفظ کنم.

CASE
    WHEN type ~ 'Adjustment|CREDIT|DSLIP'
        THEN 'In'
    WHEN type ~ 'CHECK|DEBIT|Fee|Payment|Sale'
        THEN 'Out'
    ELSE INITCAP(type) END AS clean_type

در زیر نتایج با جدید است clean_type اضافه شده است.

SELECT
    description,
    type,
    CASE
        WHEN type ~ 'Adjustment|CREDIT|DSLIP'
            THEN 'In'
        WHEN type ~ 'CHECK|DEBIT|Fee|Payment|Sale'
            THEN 'Out'
        ELSE INITCAP(type) END AS clean_type
FROM clean_transactions
ORDER BY transaction_date DESC
LIMIT 20;

description                          | type   | clean_type
------------------------------------ | ------ | ----------
TTP* LOVELY DOGGO WALKO              | Sale   | Out
BUZZ N WOODY PIZZA                   | Sale   | Out
REGIONAL LANDMARK ENERGY INC         | Sale   | Out
TST*LUCKY BREWING - CAP              | Sale   | Out
ROBBER BARON INVESTMENTS PPD ID: ... | DEBIT  | Out
TTP* LOVELY DOGGO WALKO              | Sale   | Out
GREYHOUND VETERINARY H               | Sale   | Out
NEW TECH NORTHWEST                   | Sale   | Out
REMOTE ONLINE DEPOSIT #              | DSLIP  | In
TST* DIN TAI FUNG - BELLE            | Sale   | Out
ETERNAL HOME LOANS   0000000000      | DEBIT  | Out
GOOGLE *FI DH0TCM                    | Sale   | Out
WALLSTREET BROS SAVINGS: 0000000000  | CREDIT | In
...                                  | ...    | ...

رفع دسته های نادرست و NULL

بعدی در لیست تمیز کردن بود NULL ارزش ها و دسته بندی های اشتباه در category زمینه برای درک دسته بندی ها، در اینجا مقادیر متمایز اختصاص داده شده توسط کارت های اعتباری من آمده است.

SELECT DISTINCT(category)
FROM transactions_demo
ORDER BY category;

category
---------------------
Automotive
Bills & Utilities
Education
Entertainment
Fees & Adjustments
Food & Drink
Gas
Gifts & Donations
Groceries
Health & Wellness
Home
Personal
Professional Services
Shopping
Travel

شناسایی سوابق با NULL مقادیر ساده بود، اما فهمیدن اینکه کدام تراکنش‌ها به دسته‌بندی متفاوتی نیاز دارند، کمی زمان بیشتری گرفت. برای آن سوابق، شناسایی تراکنش‌های تکرارشونده نیاز به توجه را در اولویت قرار دادم. برای این کار از کوئری زیر استفاده کردم.

برخی از نام های فروشنده در description زمینه سازگار نبودند. به عنوان مثال، شرکت خدمات وام مسکن من ظاهر شد ETERNAL HOME LOANS 000... در برخی از نقاط، اما همچنین ظاهر شد ETERNAL HOME LOANS PAYMENTS 000... در بعضی جاها به منظور گروه بندی توضیحات مشابه مانند این، من از آن استفاده کردم LEFT تابعی که فقط 14 کاراکتر اول هر توضیحات را برمی گرداند.

SELECT 
    COUNT(*),
    LEFT(description,14) AS short_description,
    category
FROM transactions_demo
WHERE amount < 0
GROUP BY short_description, category
ORDER BY count DESC;

count | short_description | category
----- | ------------------| --------
83    | TTP* LOVELY DO    | Personal
43    | PAYMENT TO EMP    | NULL
39    | COSTCO WHSE #0    | Shopping
33    | WALLSTREET BRO    | NULL
31    | CHEWY.COM         | Shopping
29    | NEIGHBORS GROC    | Groceries
27    | APPLE.COM/BILL    | Shopping
26    | ROXY'S FRIED C    | Food & Drink
25    | BUZZ N WOODY P    | Food & Drink
24    | ETERNAL HOME L    | NULL
23    | EVIL INTERNET     | Bills & Utilities
23    | REGIONAL LANDM    | Bills & Utilities
20    | SQ *NEXT DOOR     | Food & Drink
19    | COSTCO GAS #00    | Gas
19    | COOKUNITY INC     | Groceries
15    | SQ *JACK STRAN    | Personal
14    | MCDONALD'S F00    | Food & Drink
14    | GREYHOUND VETE    | Personal
14    | INVESTCORP  BU    | NULL
13    | AVG PARKING 00    | Travel
...   | ...               | ...

پس از بررسی، فهرستی از مسائل را تهیه کردم. در اینجا یک زوج وجود دارد:

همانطور که قبلاً ذکر شد، تراکنش های مرتبط با حساب بانکی من همه بودند NULL و به یک دسته بندی جدید نیاز دارد. به عنوان مثال، 24 انتقال به شرکت خدمات رهنی من انجام شد ETERNAL HOME LOANS از جایی که دسته باید تغییر کند NULL به Mortgage.
39 تراکنش به انجام شد COSTCO WHSE اختصاص داده شده است Shopping. من می خواستم اینها را تغییر دهم Groceries از آنجایی که اکثر خریدهای من در Costco غذا و مواد غذایی است.
همچنین قبلاً اشاره شد، معاملات زیادی در رابطه با مالکیت سگ وجود داشت، اما به آنها دسته بندی های مختلفی مانند Personal و Shopping. من می خواستم دسته بندی های اختصاص داده شده به طور خودکار را با یک دسته جدید جایگزین کنم Dog دسته بندی
می خواستم تجمیع کنم Automative و Gas در یک دسته Automative & Gas، بنابراین گروه بندی هزینه های مربوط به مالکیت و رانندگی ماشین من آسان تر خواهد بود.

از آنجایی که تعداد زیادی از فروشندگان و خدمات تکراری وجود نداشت، تصمیم گرفتم که ساده ترین راه حل استفاده از دیگری باشد. CASE بند به عنوان مثال، اگر توضیحات شامل CHEWY.COM، GREYHOUND VETERINARY، یا LOVELY DOGGO WALKO، سپس دسته با جایگزین می شود Dog.

پس از بررسی اغلب توصیفات تکراری، چندین الگو را شناسایی کردم که باید در موارد زیر گنجانده شود CASE بیانیه

CASE
    WHEN description ~ 'DRIVER LICENSE RENEW|VEHICLE LICENSING|INSUROTRON|PARKING|SPOTHERO|PAYBYPHONE' OR category ~ 'Gas|Automotive' 
        THEN 'Automotive & Gas'
    WHEN description ~ '^PAYMENT TO EMPIRE|Credit Card Payment' 
        THEN 'Credit card'
    WHEN description ~ '^REDEMPTION CREDIT' 
        THEN 'Points cashback'
    WHEN description ~ 'LOVELY DOGGO|GREYHOUND VETERINARY|LOCAL HOUND GROOMER|CHEWY.COM|WHO LET THE PETS OUT' 
        THEN 'Dog'
    WHEN description ~ 'PLAYSTATION|NBA League|NETFLIX|YouTube|NVIDIA|APPLE.COM/BILL|GOOGLE \*Peacock' 
        THEN 'Entertainment'
    WHEN description ~ 'COSTCO WHSE|INSTACART' 
        THEN 'Groceries'
    WHEN description ~ 'TRVL CARD BANK|PARKSRESERVATIONSWEB|TRUSTEDTRAVELER' 
        THEN 'Travel'
    WHEN description ~ 'ATM WITHDRAWAL|FOREIGN EXCHANGE RATE|NON-EMPIRE BANK ATM|^CHECK #' 
        THEN 'Misc'
    WHEN description ~ '^ETERNAL HOME LOANS' 
        THEN 'Mortgage'
    WHEN description ~ '^DEPOSIT  ID NUMBER|^IRS.*TAX REF' 
        THEN 'Other income'
    WHEN description ~ '^CORPO INC ACH PMT|^VENMO|^Zelle|^REMOTE ONLINE DEPOSIT'
        THEN 'Reimbursement'
    WHEN description ~ '^WALLSTREET BROS|^INVESTCORP|ROBBER BARON INVESTM|APA.*TREAS|^Online Transfer' 
        THEN 'Saving & Investing'
    WHEN description ~ '^CORPO INC PAYROLL' 
        THEN 'Wages & Salaries'             
    ELSE category END AS clean_category

بعد از اینکه این مورد را از آخرین بار در جستجوی من انداختم …

SELECT
    transaction_date,
    description,
    category,
    type,
    CASE
        WHEN type ~ 'Adjustment|Return'
            THEN 'Returns & Cashback'
        WHEN type ~ 'CREDIT|DSLIP'
            THEN 'Income & Deposits'
        WHEN type ~ 'CHECK|DEBIT|Fee|Payment|Sale'
            THEN 'Payments & Transfers'
        ELSE INITCAP(type) END AS clean_type,
    CASE
    WHEN description ~ 'DRIVER LICENSE RENEW|VEHICLE LICENSING|INSUROTRON|PARKING|SPOTHERO|PAYBYPHONE' OR category ~ 'Gas|Automotive' 
        THEN 'Automotive & Gas'
    WHEN description ~ '^PAYMENT TO EMPIRE|Credit Card Payment' 
        THEN 'Credit card'
    WHEN description ~ '^REDEMPTION CREDIT' 
        THEN 'Points cashback'
    WHEN description ~ 'LOVELY DOGGO|GREYHOUND VETERINARY|LOCAL HOUND GROOMER|CHEWY.COM|WHO LET THE PETS OUT' 
        THEN 'Dog'
    WHEN description ~ 'PLAYSTATION|NBA League|NETFLIX|YouTube|NVIDIA|APPLE.COM/BILL|GOOGLE \*Peacock' 
        THEN 'Entertainment'
    WHEN description ~ 'COSTCO WHSE|INSTACART' 
        THEN 'Groceries'
    WHEN description ~ 'TRVL CARD BANK|PARKSRESERVATIONSWEB|TRUSTEDTRAVELER' 
        THEN 'Travel'
    WHEN description ~ 'ATM WITHDRAWAL|FOREIGN EXCHANGE RATE|NON-EMPIRE BANK ATM|^CHECK #' 
        THEN 'Misc'
    WHEN description ~ '^ETERNAL HOME LOANS' 
        THEN 'Mortgage'
    WHEN description ~ '^DEPOSIT  ID NUMBER|^IRS.*TAX REF' 
        THEN 'Other income'
    WHEN description ~ '^CORPO INC ACH PMT|^VENMO|^Zelle|^REMOTE ONLINE DEPOSIT'
        THEN 'Reimbursement'
    WHEN description ~ '^WALLSTREET BROS|^INVESTCORP|ROBBER BARON INVESTM|APA.*TREAS|^Online Transfer' 
        THEN 'Saving & Investing'
    WHEN description ~ '^CORPO INC PAYROLL' 
        THEN 'Wages & Salaries'             
    ELSE category END AS clean_category,
    amount
FROM transactions_demo
WHERE transaction_date <= '2024-08-06' 
AND description !~ 'NEW TECH NORTHWEST'
ORDER BY transaction_date DESC
LIMIT 20;

… من مثال زیر را دریافت کردم clean_category ستون

description                               | category          | clean_category
----------------------------------------- | ----------------- | ---------------
ROBBER BARON INVESTMENTS PPD ID: 00000... | NULL              | Saving & Investing
GREYHOUND VETERINARY H                    | Personal          | Dog
TTP* LOVELY DOGGO WALKO                   | Personal          | Dog
REMOTE ONLINE DEPOSIT #          0        | NULL              | Reimbursement
TST* DIN TAI FUNG - BELLE                 | Food & Drink      | Food & Drink
Uniqlo USA LLC                            | Shopping          | Shopping
ETERNAL HOME LOANS   0000000000      W... | NULL              | Mortgage
GOOGLE *FI DH0TCM                         | Bills & Utilities | Bills & Utilities
TTP* LOVELY DOGGO WALKO                   | Personal          | Dog
Credit Card Payment - Thank You           | NULL              | Credit card
PAYMENT TO EMPIRE BANK CARD ENDING IN ... | NULL              | Credit card
GOOGLE *Peacock                           | Shopping          | Entertainment
AMC 0000 ONLINE                           | Entertainment     | Entertainment
NEIGHBORS GROCERY OUT                     | Groceries         | Groceries
WALLSTREET BROS SAVINGS: 0000000000       | NULL              | Saving & Investing
TAQUERIA EL                               | Food & Drink      | Food & Drink
GREYHOUND VETERINARY H                    | Personal          | Dog
SQ *JACK STRANDS SALON                    | Personal          | Personal
FANDANGO                                  | Entertainment     | Entertainment
TRVL CARD BANK  ONLINE PMT CKF00000000... | NULL              | Travel

حذف هرج و مرج از توضیحات

این description فیلد شامل یک یادداشت در مورد هر تراکنش است. برای مثال، می‌تواند فروشنده‌ای باشد که از آن چیزی خریدم یا نام حساب پس‌اندازی که به آن پول فرستادم.

SELECT 
    transaction_date,
    description
FROM transactions_demo
WHERE description ~* '^Google.+Fi'
ORDER BY transaction_date DESC
LIMIT 25;

transaction_date | description       
---------------- | ----------------- 
2024-07-31       | GOOGLE *FI DH0TCM 
2024-07-01       | GOOGLE *FI M0zWRQ 
2024-05-31       | GOOGLE *FI 00CNX0 
2024-05-01       | GOOGLE *FI 0GB00X 
2024-03-31       | GOOGLE *FI 0S0HNC 
2024-03-02       | GOOGLE *FI XKW00N 
2024-01-31       | GOOGLE *FI D0J0RZ 
2023-12-31       | GOOGLE *FI G0JRTL 
2023-12-01       | GOOGLE *FI L0CQ00 
2023-10-31       | GOOGLE *FI 0HFBR0 
2023-10-01       | GOOGLE *FI vvRRSB 
2023-08-31       | GOOGLE *FI CV00MN 
2023-07-31       | GOOGLE *FI 00LRXC 
...              | ...

اگر بخواهم به سؤالی پاسخ دهم که «سال گذشته چقدر برای خدمات تلفنی خرج کردم»، شرح را به چیزی شبیه به استاندارد کردن Google Fi گروه بندی تراکنش ها را آسان تر می کند.

در مرحله بعد، این عمدتا یک مشکل زیبایی شناختی است، اما نگاهی به معاملات غذاخوری و بیرون‌آوری بیندازید.

SELECT
    description,
    category
FROM transactions_demo
WHERE category = 'Food & Drink'
ORDER BY transaction_date DESC
LIMIT 20;

'description'             | 'category'   
------------------------- | ------------ 
MESOPOTAMIA MIX           | Food & Drink 
TST*LUCKY BREWING - CAP   | Food & Drink 
HOLE IN WALL THAI LLC     | Food & Drink 
TST*ROCKET TACO - TOWN    | Food & Drink 
CAFE BRIO                 | Food & Drink 
SQ *RED FISH BLUE FISH    | Food & Drink 
SQ *CRAFT (VICTORIA) LTD. | Food & Drink 
FRIENDS & FAMILY BAKE | Food & Drink 
BUZZ N WOODY PIZZA        | Food & Drink 
TST* DIN TAI FUNG         | Food & Drink 
TAQUERIA EL               | Food & Drink 
TWSP PRK ADAM GOLF RES0   | Food & Drink 
SQ *FATS WINGS AND WAFFLE | Food & Drink 
SQ *BEANFISH              | Food & Drink 
SQ *FATS WINGS AND WAFFLE | Food & Drink 
TST* RETURNER CINEMA      | Food & Drink 
...                       | ...

رستوران ها و بسیاری از مشاغل دیگر از خدمات پردازش پرداخت مانند Square یا Toast استفاده می کنند که یک رشته کوتاه مانند درج می کند SQ * و TST* در ابتدای هر توضیح من می خواستم این رشته های اضافی (و سایر کاراکترها) را حذف کنم تا خوانایی داده ها را افزایش دهم. مثل این:

SQ *RED FISH BLUE FISH –> RED FISH BLUE FISH
SQ *CRAFT (VICTORIA) LTD. –> CRAFT VICTORIA
TST*LUCKY BREWING - CAP –> LUCKY BREWING - CAP

سپس توضیحاتی را که شامل چند کاراکتر خاص بود بررسی کردم:

SELECT
    description,
    category
FROM transactions_demo
WHERE description ~ '\*|\#|\-' 
ORDER BY transaction_date DESC
LIMIT 25;

description                        | category          
---------------------------------- | ----------------- 
00000 - 000 DEXTER JUNO            | Travel            
COSTCO WHSE #0000                  | Shopping          
REMOTE ONLINE DEPOSIT #     000000 | NULL              
GOOGLE *Peacock                    | Shopping          
B\&H PHOTO 000-000-0000        | Shopping          
AMAZON MKTPL*RJ0DG00F0             | Shopping          
LOWES #00000*                      | Home              
COSTCO GAS #0000                   | Gas

بر اساس آن پرس و جو، من می خواستم کاراکترهای اضافی (و اعداد) مانند این را حذف کنم:

COSTCO WHSE #0000 –> COSTCO WHSE
COSTCO GAS #0000 –> COSTCO GAS
LOWES #00000* –> LOWES

و کاراکترهای خاص مانند علامت در این توضیحات را اصلاح کنید:

SELECT description
FROM transactions_demo
WHERE description ~* '\d+'
ORDER BY transaction_date DESC
LIMIT 25;

description     
--------------------------------------------------------------
ROBBER BARON INVESTMENTS PPD ID: 0000000000
ETERNAL HOME LOANS   0000000000      WEB ID: 0000000000
PAYMENT TO EMPIRE BANK CARD ENDING IN 0000 00/00
WALLSTREET BROS SAVINGS: 0000000000
TRVL CARD BANK  ONLINE PMT CKF000000000POS WEB ID: 0000000000
APA  TREAS 000     MISC PAY                 PPD ID: 0000000000
APA  TREAS 000     MISC PAY                 PPD ID: 0000000000
...
...

در این مواقع می خواستم چیزی شبیه به این را تغییر دهم ETERNAL HOME LOANS 0000000000 WEB ID: 0000000000 فقط ETERNAL HOME LOANS.

عبارات منظم برای نجات

با کاوش در ستون توضیحات و بررسی رکوردهای متداول، چندین الگو را شناسایی کردم که باید حذف یا اصلاح شوند. از اونجا یه سری تو در تو نوشتم REPLACE و REGEXP_REPLACE بندهایی که توضیحات را پاک می کند.

-- Change first letter to uppercase, rest of string lower case
TRIM(INITCAP(
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE(
                REGEXP_REPLACE(
                    REPLACE(
                        REPLACE(description,'''',''),
                    -- Fix ampersands (&)
                    '&', '&'),
                -- Replace strings of letters and numbers after a special character (as long as it appears after the eighth char)
                '(?\W\d+.+$',' ''g'),
            -- Replace certain patterns (e.g., 'TST*'), special characters, unwanted strings(e.g. 'LLC')
            '^SP\s|^[a-zA-Z]{2,3}\*|^[a-zA-Z]{2,3} \*|\s[a-zA-Z]{1,3}\d+.+$|\s[a-zA-Z]{3}\s[a-zA-Z]{2}\:\s.|\d|[^\w\s&.]|\sLLC$|\sINC$|\sLTD$|\sLLC\.$|\sINC\.$|\sInc\.$|\sLTD\.$', ' ', 'g'),
        -- Replace single characters hanging at the end of a string (e.g. 'Veterinary H' --> 'Veterinary')
        '\s[a-zA-Z]$|\s[a-zA-Z]\s+$','', 'g'),  
    -- Change multiple spaces into one space
    '\s+', ' ', 'g')
)) AS clean_description

SELECT 
    transaction_date,
    description,
    TRIM(INITCAP(
        REGEXP_REPLACE(
            REGEXP_REPLACE(
                REGEXP_REPLACE(
                    REGEXP_REPLACE(
                        REPLACE(
                            REPLACE(description,'''',''),
                        '&', '&'),
                    '(?\W\d+.+$',' ''g'),
                '^SP\s|^[a-zA-Z]{2,3}\*|^[a-zA-Z]{2,3} \*|\s[a-zA-Z]{1,3}\d+.+$|\s[a-zA-Z]{3}\s[a-zA-Z]{2}\:\s.|\d|[^\w\s&.]|\sLLC$|\sINC$|\sLTD$|\sLLC\.$|\sINC\.$|\sInc\.$|\sLTD\.$', ' ', 'g'),
            '\s[a-zA-Z]$|\s[a-zA-Z]\s+$','', 'g'),  
        '\s+', ' ', 'g')
        )) AS clean_description
FROM transactions_demo
ORDER BY transaction_date DESC
LIMIT 45;

'description'                            | 'clean_description'                   
---------------------------------------- | ------------------------------------- 
TTP* LOVELY DOGGO WALKO                  | Lovely Doggo Walko                    
TST*LUCKY BREWING - CAP                  | Lucky Brewing Cap                     
00000 - 000 DEXTER JUNO                  | Dexter Juno                           
TTP* LOVELY DOGGO WALKO                  | Lovely Doggo Walko                    
EVIL INTERNET & CABLE CORP               | Evil Internet & Cable Corp            
HOLE IN WALL THAI LLC                    | Hole In Wall Thai                     
COSTCO WHSE #0000                        | Costco Whse                           
COAST VICTORIA HOTE &                | Coast Victoria Hote &                 
PY *FRS CLIPPER                          | Frs Clipper                           
CHEWY.COM                                | Chewy.Com                             
SQ *CRAFT (VICTORIA) LTD.                | Craft Victoria                        
IRISH TIMES PUB                          | Irish Times Pub                       
FRIENDS & FAMILY BAKE                | Friends & Family Bake                 
SQ *RED FISH BLUE FISH                   | Red Fish Blue Fish                    
ROBBER BARON INVESTMENTS PPD ID: 0000... | Robber Baron Investments              
TTP* LOVELY DOGGO WALKO                  | Lovely Doggo Walko                    
GREYHOUND VETERINARY H                   | Greyhound Veterinary                  
REMOTE ONLINE DEPOSIT #          0       | Remote Online Deposit                 
ETERNAL HOME LOANS  0000000  WEB ID: ... | Eternal Home Loans                    
GOOGLE *FI DH0TCM                        | Google Fi                             
Uniqlo USA LLC                           | Uniqlo Usa                            
TTP* LOVELY DOGGO WALKO                  | Lovely Doggo Walko                    
PAYMENT TO EMPIRE BANK CARD ENDING IN... | Payment To Empire Bank Card Ending In 
...                                      | ...

…و کار خیلی خوبی انجام داد!!!!

با این حال، کامل نبود. به عنوان مثال، بیایید به خرید از آمازون نگاه کنیم.

description            | clean_description  
---------------------- | ------------------ 
AMAZON MKTPL*RJ0DG00F0 | Amazon Mktpl Rj Dg 
AMZN Mktp US*R00000RQ0 | Amzn Mktp Us R Rq  
AMAZON MKTPL*000GV0A00 | Amazon Mktpl       
AMZN Mktp US*BZ0E00T00 | Amzn Mktp Us Bz E  
Amazon.com             | Amazon.Com         
AMZN Mktp US*DK00L00F0 | Amzn Mktp Us Dk L  
Amazon.com*R00Y00R00   | Amazon.Com R Y     
...

برای فروشندگان و خدمات مکرر، تصمیم گرفتم سریعتر باشد که برخی از قوانین تخصصی را به آن اضافه کنم CASE بیانیه زمانی که قبلاً فیلد دسته بندی را اصلاح می کردم، قبلاً لیستی از فروشندگان و الگوهای تکراری در description زمینه، بنابراین ایجاد بند دیگری مانند زیر بسیار آسان بود.

TRIM(CASE
    WHEN description ~ 'PAYBYPHONE'
        THEN 'PayByPhone Parking'
    WHEN description ~* 'GOOGLE.*FI'
        THEN 'Google Fi'
    WHEN description ~ 'LOVELY DOGGO WALKO'
        THEN 'Lovely Doggo Walko'
    WHEN description ~* 'PLAYSTATION'
        THEN 'PlayStation Network'
    WHEN description ~ 'SOUTHERN CHICKEN AND WAFF'
        THEN 'Southern Chicken And Waffle'
    WHEN description ~ 'NEIGHBORS GROCERY OUT'
        THEN 'Neighbors Grocery Outlet'
    WHEN description ~ 'AmazonStores|Amazon Fresh'
        THEN 'Amazon Fresh'
    WHEN description ~ 'WALLSTREET BROS'
        THEN 'Wallstreet Bros Savings'
    WHEN description ~* 'AMZN Mktp|Amazon.com|AMAZON MKTPL'
        THEN 'Amazon.com'
    WHEN description ~ 'ATM WITHDRAWAL'
        THEN 'ATM Withdrawal'
    WHEN description ~ 'ETERNAL HOME LOANS'
        THEN 'Eternal Home Loans'
    WHEN description ~ 'AIRBNB'
        THEN 'Airbnb'
    WHEN description ~ 'LYFT.*RIDE'
        THEN 'Lyft Ride'
    WHEN description ~* 'Uniqlo'
        THEN 'Uniqlo'
    ELSE INITCAP(
        REGEXP_REPLACE(
            REGEXP_REPLACE(
                REGEXP_REPLACE(
                    REGEXP_REPLACE(
                        REPLACE(
                            REPLACE(description,'''',''),
                        '&', '&'),
                    '(?\W\d+.+$',' ''g'),
                '^SP\s|^[a-zA-Z]{2,3}\*|^[a-zA-Z]{2,3} \*|\s[a-zA-Z]{1,3}\d+.+$|\s[a-zA-Z]{3}\s[a-zA-Z]{2}\:\s.|\d|[^\w\s&.]|\sLLC$|\sINC$|\sLTD$|\sLLC\.$|\sINC\.$|\sInc\.$|\sLTD\.$', ' ', 'g'),
            '\s[a-zA-Z]$|\s[a-zA-Z]\s+$','', 'g'),  
        '\s+', ' ', 'g')
    ) END) AS clean_description

پرس و جو را به یک عبارت جدول مشترک منتقل کنید

در این مرحله، جدید clean_description، clean_category، و clean_type ستون‌ها بیشتر داده‌های ناسازگار و گمشده را ثابت کردند. وقت آن بود که شروع به جمع بندی و تجزیه و تحلیل هزینه هایم کنم! اما ابتدا … اجازه دهید یک مرحله مهم دیگر را بررسی کنیم.

وقتی سعی کردم تراکنش ها را با استفاده از a فیلتر کنم WHERE بند با clean_category همانطور که در زیر مشاهده می کنید …

SELECT
    transaction_date,
    description,
    CASE
        WHEN description ~ 'DRIVER LICENSE RENEW|VEHICLE LICENSING|INSUROTRON|PARKING|SPOTHERO|PAYBYPHONE' OR category ~ 'Gas|Automotive' 
            THEN 'Automotive & Gas'
        WHEN description ~ '^PAYMENT TO EMPIRE|Credit Card Payment' 
            THEN 'Credit card'
        WHEN description ~ '^REDEMPTION CREDIT' 
            THEN 'Points cashback'
        WHEN description ~ 'LOVELY DOGGO|GREYHOUND VETERINARY|LOCAL HOUND GROOMER|CHEWY.COM|WHO LET THE PETS OUT' 
            THEN 'Dog'
        WHEN description ~ 'PLAYSTATION|NBA League|NETFLIX|YouTube|NVIDIA|APPLE.COM/BILL|GOOGLE \*Peacock' 
            THEN 'Entertainment'
        WHEN description ~ 'PHO SUP SHOP|BLING BLING ASIAN MARKET' 
            THEN 'Food & Drink' 
        WHEN description ~ 'COSTCO WHSE|INSTACART' 
            THEN 'Groceries'
        WHEN description ~ 'TRVL CARD BANK|PARKSRESERVATIONSWEB|TRUSTEDTRAVELER' 
            THEN 'Travel'
        WHEN description ~ 'ATM WITHDRAWAL|FOREIGN EXCHANGE RATE|NON-EMPIRE BANK ATM|^CHECK #' 
            THEN 'Misc'
        WHEN description ~ '^ETERNAL HOME LOANS' 
            THEN 'Mortgage'
        WHEN description ~ '^DEPOSIT  ID NUMBER|^IRS.*TAX REF' 
            THEN 'Other income'
        WHEN description ~ '^CORPO INC ACH PMT|^VENMO|^Zelle|^REMOTE ONLINE DEPOSIT'
            THEN 'Reimbursement'
        WHEN description ~ '^WALLSTREET BROS|^INVESTCORP|ROBBER BARON INVESTM|APA.*TREAS|^Online Transfer' 
            THEN 'Saving & Investing'
        WHEN description ~ '^CORPO INC PAYROLL' 
            THEN 'Wages & Salaries'             
        ELSE category END AS clean_category,
    amount
FROM transactions_demo
-- Only show results from the Food & Drink category
WHERE clean_category = 'Food & Drink'

…من با این خطا مواجه شدم.

ERROR:  column "clean_category" does not exist
LINE 98: WHERE clean_category = 'Food & Drink'
               ^

چرا؟ خوب، هنگام حل یک پرس و جو، SQL محتوای موجود در آن را بررسی می کند WHERE بند قبل از حل و فصل SELECT بخشی از عبارت (منطقی است زیرا ما فقط داده هایی را می خواهیم که با آن مطابقت داشته باشند WHERE بند مورد پردازش و بازگرداندن). از آنجایی که CASE بند به همراه بقیه موارد پردازش می شود SELECT بخش، آن زمان وجود ندارد WHERE حل می شود. پس من clean_category زمانی که ستون وجود نداشت WHERE بند حل شد

برای رفع این مشکل، پرس و جو را به یک CTE (عبارت جدول رایج) منتقل کردم. به این ترتیب، ستون‌های «تمیز» من در چیزی شبیه به یک جدول موقت ذخیره می‌شوند و من می‌توانم به داده‌های پاک‌شده هنگام نوشتن یک اشاره کنم. WHERE بند در اینجا نمونه ای از پرس و جو پس از انتقال آن به یک CTE است.

WITH 
    -- Common table expression (CTE) containing my cleaned data 
    clean_transactions AS (
        SELECT
            transaction_id,
            transaction_date,
            -- Replace common descriptions and remove extra characters and symbols
            TRIM(CASE
                WHEN description ~ 'PAYBYPHONE'
                    THEN 'PayByPhone Parking'
                WHEN description ~* 'GOOGLE.*FI'
                    THEN 'Google Fi'
                WHEN description ~ 'LOVELY DOGGO WALKO'
                    THEN 'Lovely Doggo Walko'
                WHEN description ~* 'PLAYSTATION'
                    THEN 'PlayStation Network'
                WHEN description ~ 'SOUTHERN CHICKEN AND WAFF'
                    THEN 'Southern Chicken And Waffle'
                WHEN description ~ 'NEIGHBORS GROCERY OUT'
                    THEN 'Neighbors Grocery Outlet'
                WHEN description ~ 'AmazonStores|Amazon Fresh'
                    THEN 'Amazon Fresh'
                WHEN description ~ 'WALLSTREET BROS'
                    THEN 'Wallstreet Bros Savings'
                WHEN description ~* 'AMZN Mktp|Amazon.com|AMAZON MKTPL'
                    THEN 'Amazon.com'
                WHEN description ~ 'ATM WITHDRAWAL'
                    THEN 'ATM Withdrawal'
                WHEN description ~ 'ETERNAL HOME LOANS'
                    THEN 'Eternal Home Loans'
                WHEN description ~ 'AIRBNB'
                    THEN 'Airbnb'
                WHEN description ~ 'LYFT.*RIDE'
                    THEN 'Lyft Ride'
                WHEN description ~* 'Uniqlo'
                    THEN 'Uniqlo'
                ELSE INITCAP(
                    REGEXP_REPLACE(
                        REGEXP_REPLACE(
                            REGEXP_REPLACE(
                                REGEXP_REPLACE(
                                    REPLACE(
                                        REPLACE(description,'''',''),
                                    '&', '&'),
                                -- Replace strings of letters and numbers after a special character (as long as it appears after the eighth char)
                                '(?\W\d+.+$',' ''g'),
                            -- Replace certain patterns (e.g., 'TST*'), special characters, unwanted strings(e.g. 'LLC')
                            '^SP\s|^[a-zA-Z]{2,3}\*|^[a-zA-Z]{2,3} \*|\s[a-zA-Z]{1,3}\d+.+$|\s[a-zA-Z]{3}\s[a-zA-Z]{2}\:\s.|\d|[^\w\s&.]|\sLLC$|\sINC$|\sLTD$|\sLLC\.$|\sINC\.$|\sInc\.$|\sLTD\.$', ' ', 'g'),
                        -- Replace single characters hanging at the end of a string (e.g. 'Veterinary H' --> 'Veterinary')
                        '\s[a-zA-Z]$|\s[a-zA-Z]\s+$','', 'g'),  
                    -- Change multiple spaces into one space
                    '\s+', ' ', 'g')
                ) END) AS clean_description,
            -- Fix wrong categories and add new ones (e.g., Dog, Mortgage, Wages & Salary)
            CASE
                WHEN description ~ 'DRIVER LICENSE RENEW|VEHICLE LICENSING|INSUROTRON|PARKING|SPOTHERO|PAYBYPHONE' OR category ~ 'Gas|Automotive' 
                    THEN 'Automotive & Gas'
                WHEN description ~ '^PAYMENT TO EMPIRE|Credit Card Payment' 
                    THEN 'Credit card'
                WHEN description ~ '^REDEMPTION CREDIT' 
                    THEN 'Points cashback'
                WHEN description ~ 'LOVELY DOGGO|GREYHOUND VETERINARY|LOCAL HOUND GROOMER|CHEWY.COM|WHO LET THE PETS OUT' 
                    THEN 'Dog'
                WHEN description ~ 'PLAYSTATION|NBA League|NETFLIX|YouTube|NVIDIA|APPLE.COM/BILL|GOOGLE \*Peacock' 
                    THEN 'Entertainment'
                WHEN description ~ 'COSTCO WHSE|INSTACART' 
                    THEN 'Groceries'
                WHEN description ~ 'TRVL CARD BANK|PARKSRESERVATIONSWEB|TRUSTEDTRAVELER' 
                    THEN 'Travel'
                WHEN description ~ 'ATM WITHDRAWAL|FOREIGN EXCHANGE RATE|NON-EMPIRE BANK ATM|^CHECK #' 
                    THEN 'Misc'
                WHEN description ~ '^ETERNAL HOME LOANS' 
                    THEN 'Mortgage'
                WHEN description ~ '^DEPOSIT  ID NUMBER|^IRS.*TAX REF' 
                    THEN 'Other income'
                WHEN description ~ '^CORPO INC ACH PMT|^VENMO|^Zelle|^REMOTE ONLINE DEPOSIT'
                    THEN 'Reimbursement'
                WHEN description ~ '^WALLSTREET BROS|^INVESTCORP|ROBBER BARON INVESTM|APA.*TREAS|^Online Transfer' 
                    THEN 'Saving & Investing'
                WHEN description ~ '^CORPO INC PAYROLL' 
                    THEN 'Wages & Salaries'             
                ELSE category END AS clean_category,
            -- Group transaction type to simplify grouping later
            CASE
                WHEN type ~ 'Adjustment|Return'
                    THEN 'Returns & Cashback'
                WHEN type ~ 'CREDIT|DSLIP'
                    THEN 'Income & Deposits'
                WHEN type ~ 'CHECK|DEBIT|Fee|Payment|Sale'
                    THEN 'Payments & Transfers'
                ELSE INITCAP(type) END AS clean_type,
            amount,
            -- Add year, quarter, and month for summarizing transactions later
            EXTRACT(YEAR FROM transaction_date) AS year,
            TO_CHAR(transaction_date,'YYYY"_Q"Q') as qtr_string,
            TO_CHAR(transaction_date,'YYYY_MM') as month,
            account_id
        FROM transactions_demo
    )
-- Query referencing the new 'clean_transactions' CTE
SELECT
    transaction_date,
    clean_description,
    amount
FROM clean_transactions
WHERE clean_category = 'Food & Drink'
ORDER BY transaction_date DESC;

transaction_date | clean_description     |  amount  
---------------- | --------------------- | ------- 
2024-08-21       | Buzz N Woody Pizza    |  -50.33  
2024-08-17       | Lucky Brewing Cap     |   -7.42      
2024-08-17       | Mesopotamia Mix       |  -29.06  
2024-08-13       | Hole In Wall Thai     |  -52.50  
2024-08-12       | Rocket Taco Town      |  -33.59  
2024-08-11       | Coast Victoria Hote & |  -42.62  
2024-08-10       | Cafe Brio             | -121.86  
2024-08-09       | Friends & Family Bake |   -7.90      
2024-08-09       | Red Fish Blue Fish    |  -32.86  
2024-08-09       | Irish Times Pub       |  -74.75  
2024-08-09       | Craft Victoria        |  -31.78  
2024-08-03       | Din Tai Fung Belle    |  -40.10  
2024-07-26       | Taqueria El           |  -24.89  
...              | ...                   |     ...

بله!!! با clean_transactions CTE، من می توانم در نهایت خلاصه و تجزیه و تحلیل معاملات را شروع کنم.

چند پرس و جو سریع

برای شروع، اجازه دهید کل هزینه های هر دسته را به همراه درصد کل هزینه های آن سال بررسی کنیم.

WITH
    ...
    ...
    ...
SELECT 
    year,
    clean_category,
    SUM(amount) AS total_spend,
    ROUND(SUM(amount) / (SUM(SUM(amount)) OVER(PARTITION BY year))*100,2)  AS pct_of_spend
FROM clean_transactions
WHERE clean_category NOT IN ('Wages & Salaries','Credit card','Saving & Investing','Other income','Points cashback')
GROUP BY year, clean_category
ORDER BY year DESC, total_spend;

year | clean_category        | total_spend | pct_of_spend 
---- | --------------------- | ----------- | ------------ 
2024 | Mortgage              |   -16196.71 |        38.78 
2024 | Shopping              |    -5309.93 |        12.71 
2024 | Travel                |    -4350.83 |        10.42 
2024 | Dog                   |    -3255.04 |         7.79 
2024 | Bills & Utilities     |    -3208.90 |         7.68 
2024 | Food & Drink          |    -3021.83 |         7.24 
2024 | Groceries             |    -1830.94 |         4.38 
2024 | Automotive & Gas      |    -1446.29 |         3.46 
2024 | Misc                  |     -760.00 |         1.82 
2024 | Home                  |     -715.45 |         1.71 
2024 | Entertainment         |     -687.91 |         1.65 
2024 | Personal              |     -543.92 |         1.30 
2024 | Professional Services |     -393.02 |         0.94 
2024 | Health & Wellness     |     -170.78 |         0.41 
2024 | Gifts & Donations     |      -76.00 |         0.18 
...  | ...                   |         ... |          ...

یا فقط برای سرگرمی، می‌توانم پربازدیدترین رستوران‌هایم را در سال 2024 بررسی کنم.

WITH
    ...
    ...
    ...
SELECT 
    COUNT(clean_description) AS visits,
    clean_description AS restaurant
FROM clean_transactions 
WHERE clean_category IN('Food & Drink')
    AND year = '2024'
GROUP BY clean_description
ORDER BY visits DESC;

visits | restaurant                 
------ | ---------------------------
11     | Roxys Fried Chicken        
6      | Buzz N Woody Pizza         
4      | Mesopotamia Mix            
4      | Mcdonalds                  
3      | Southern Chicken And Waffle
2      | Fantasy Donut Force        
2      | Taqueria El                
2      | Twsp Prk Adam Golf Res     
2      | Taco Tuesday               
2      | Next Door Bakery           
...    | ...

…بنابراین، بله، ممکن است مشکل مرغ سوخاری در این خانواده وجود داشته باشد.