SELECT TOP 5 *
FROM q10_payments
payer | receiver | payment_id | Transaction_date | Amount |
---|---|---|---|---|
14 | 7 | 1 | 2020-06-30 | 45 |
14 | 7 | 2 | 2020-07-28 | 17 |
14 | 7 | 3 | 2020-07-29 | 24 |
1 | 7 | 4 | 2020-08-27 | 4 |
1 | 7 | 5 | 2020-08-28 | 89 |
אגרגציה על פני מגוון עמודות
March 29, 2025
ברוכים הבאים לתרגיל העשירי!
בתרגיל הבא נתרגל לקיחה של ערכים מעמודות לפי תנאי. זה תאור קצת כללי לתרגיל (עם 2 השאלות), אבל זה בגדול מה שהיה לי בראש כשניסיתי לתאר את האתגרים שאני מנסה לשתף פה.
הגעתם למקום שעוד לא יצר תהליכי ETL מוסדרים ולכן אתם בעיקר עובדים עם דאטה מ-OLTP.
למטה תוכלו לראות דיאגרמת ישויות קשרים של הטבלאות ואת השם של כל אחת מעליה (עם מפתח מחבר של Payment_id). בגדול יש לנו טבלת משלמים ואילו המקבלים השונים וגובה הסכום פזורים בטבלאות שונות.
צרו פלט שיציג את היוזר המשלם, היוזר המקבל וכמה הוא קיבל סה”כ מאותו משלם, ללא קשר לסוג התשלום. שימו לב כי ישנם תשלומים בלי מידע על סוג התשלום (התעלמו מהם)
פה כבר יש לנו את טבלת התשלומים המלאה:
payer | receiver | payment_id | Transaction_date | Amount |
---|---|---|---|---|
14 | 7 | 1 | 2020-06-30 | 45 |
14 | 7 | 2 | 2020-07-28 | 17 |
14 | 7 | 3 | 2020-07-29 | 24 |
1 | 7 | 4 | 2020-08-27 | 4 |
1 | 7 | 5 | 2020-08-28 | 89 |
אנחנו מעוניינים ליצור טבלת מידע על כל המשלמים ומקבלי התשלום.
לשם כך, הדפיסו טבלה שתציג לנו עמודת יוזר מקור, עמודת יוזר רפרנס, כמה תשלומים היוזר רפרנס קיבל מהמקור, כמה תשלומים היוזר רפרנס שלח למקור ומה היה התאריך האחרון שנשלח ביניהם תשלום (לא משנה הכיוון). יוזר רפרנס הוא כל יוזר שהיה לו קשר כלשהו - כמשלם או מקבל - ליוזר המקור שלנו. אנחנו רוצים שכל משתמש ייחודי בטבלה (משלם/מקבל) יופיע כיוזר מקור
את הפלט סדרו לפי מספר הקשרים השונים של כל יוזר בסדר יורד. כלומר, היוזר שהופיע בהכי הרבה קשרים יהיה ראשון, אחריו היוזר עם מספר המופעים הגדול אחריו וכן הלאה.
נסו בחלון טקסט למטה (SQLite):
CREATE TABLE q10_payers (
payment_id INT,
payer INT
);
INSERT INTO q10_payers (payment_id, payer ) VALUES
('1', '14 '),
('2', '14 '),
('3', '14 '),
('4', '1 '),
('5', '1 '),
('6', '6 '),
('7', '6 '),
('8', '7 '),
('9', '7 '),
('10', '3 '),
('11', '3 '),
('12', '3 '),
('13', '3 '),
('14', '7 '),
('15', '7 '),
('16', '7 '),
('17', '4 '),
('18', '12 '),
('19', '12 '),
('20', '12 ');
CREATE TABLE Cash (
receiver INT,
payment_id INT,
Amount INT
);
INSERT INTO Cash (payment_id, receiver, Amount ) VALUES
('1', '7', '45 '),
('4', '1', '4 '),
('13','3', '84 '),
('18','7', '94 '),
('19','7', '25 '),
('20','7', '80 ');
CREATE TABLE Bit (
receiver INT,
payment_id INT,
amount INT
);
INSERT INTO Bit (payment_id, receiver, amount ) VALUES
('2', '7', '17 '),
('5', '7', '89 '),
('10','1', '31 '),
('14','22', '23 '),
('16','22', '63 '),
('17','7', '75 ');
CREATE TABLE checks (
receiver INT,
payment_id INT,
amount INT
);
INSERT INTO Checks (payment_id, receiver, amount ) VALUES
('7', '7', '47 '),
('8', '21','42 '),
('15','22', '1 '),
('6', '7', '26 ');
CREATE TABLE q10_payments (
payer INT,
receiver INT,
payment_id INT,
Transaction_date DATE,
Amount INT
);
INSERT INTO q10_payments (payer, receiver, payment_id, Transaction_date, Amount ) VALUES
('14', '7', '1', '2020-06-30', '45'),
('14', '7', '2', '2020-07-28', '17'),
('14', '7', '3', '2020-07-29', '24'),
('1', '7', '4', '2020-08-27', '4'),
('1', '7', '5', '2020-08-28', '89'),
('6', '7', '6', '2020-08-29', '26'),
('6', '7', '7', '2020-08-30', '47'),
('7', '21', '8', '2020-08-31', '42'),
('7', '21', '9', '2020-09-01', '52'),
('3', '1', '10', '2020-09-04', '31'),
('3', '1', '11', '2020-09-05', '92'),
('3', '1', '12', '2020-09-06', '54'),
('3', '1', '13', '2020-09-07', '84'),
('7', '22', '14', '2020-09-08', '23'),
('7', '22', '15', '2020-09-09', '1'),
('7', '22', '16', '2020-09-10', '63'),
('4', '7', '17', '2021-05-28', '75'),
('12', '7', '18', '2021-06-30', '94'),
('12', '7', '19', '2021-07-28', '25'),
('12', '7', '20', '2021-07-29', '80');
payer | receiver | total_received |
---|---|---|
1 | 1 | 4 |
3 | 1 | 31 |
3 | 3 | 84 |
1 | 7 | 89 |
4 | 7 | 75 |
6 | 7 | 73 |
12 | 7 | 199 |
14 | 7 | 62 |
7 | 21 | 42 |
7 | 22 | 87 |
T-SQL
SELECT payer,
COALESCE(cash.receiver, bit.receiver, Checks.receiver) AS receiver,
sum(coalesce(cash.amount, bit.amount, Checks.amount)) as total_received
FROM Q10_PAYERS payers
LEFT JOIN Cash on cash.payment_id = payers.payment_id
LEFT JOIN Bit ON BIT.payment_id = payers.payment_id
LEFT JOIN Checks ON Checks.payment_id = payers.payment_id
WHERE COALESCE(cash.receiver, bit.receiver, Checks.receiver) IS NOT NULL
GROUP BY payer, COALESCE(cash.receiver, bit.receiver, Checks.receiver)
-- SELECT * FROM Checks
source | reference | n_payments_from_source | n_payments_to_source | last_transaction |
---|---|---|---|---|
7 | 1 | 0 | 2 | 2020-08-28 |
7 | 4 | 0 | 1 | 2021-05-28 |
7 | 6 | 0 | 2 | 2020-08-30 |
7 | 12 | 0 | 3 | 2021-07-29 |
7 | 14 | 0 | 3 | 2020-07-29 |
7 | 21 | 2 | 0 | 2020-09-01 |
7 | 22 | 3 | 0 | 2020-09-10 |
1 | 3 | 0 | 4 | 2020-09-07 |
1 | 7 | 2 | 0 | 2020-08-28 |
3 | 1 | 4 | 0 | 2020-09-07 |
T-SQL, (מראה רק את ה-10 שורות הראשונות)
with entites as (
select payer as entity
from q10_payments
union
select receiver as entity
from q10_payments
)
SELECT ent.Entity as source,
case when ent.entity = q10.payer then q10.receiver else q10.payer end as reference,
count(case when ent.entity = q10.payer then payment_id else null end) as n_payments_from_source,
count(case when ent.entity = q10.receiver then payment_id else null end) as n_payments_to_source,
max(transaction_date) as last_transaction
FROM entites ent
left join q10_payments q10 on q10.payer = ent.entity OR ent.entity = q10.receiver
group by ent.Entity,
case when ent.entity = q10.payer then q10.receiver else q10.payer end
order by count(*) over(partition by ent.Entity) desc