Changes between Version 16 and Version 17 of ReportsProcedures


Ignore:
Timestamp:
09/30/25 13:49:19 (4 weeks ago)
Author:
223075
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ReportsProcedures

    v16 v17  
    2929{{{
    3030SELECT
    31 user_id, username, email,
    32 spend_packages, spend_merch, total_spend,
    33 RANK() OVER (ORDER BY total_spend DESC) AS spend_rank
     31  user_id, username, email,
     32  spend_packages, spend_merch, total_spend,
     33  RANK() OVER (ORDER BY total_spend DESC) AS spend_rank
    3434FROM vw_user_spend
    3535ORDER BY total_spend DESC, user_id;
    … …  
    4040Π_{user_id,username,email,spend_packages,spend_merch,total_spend}
    4141(
    42 User
    43 ⟕ (γ_{user_id; SUM(price)→spend_packages}(User_Purchased_Package ⋈ Package))
    44 ⟕ (γ_{user_id; SUM(price)→spend_merch}(User_Purchased_Merch ⋈ Merch_Items))
     42  User
     43  ⟕ (γ_{user_id; SUM(price)→spend_packages}(User_Purchased_Package ⋈ Package))
     44  ⟕ (γ_{user_id; SUM(price)→spend_merch}(User_Purchased_Merch ⋈ Merch_Items))
    4545)
    4646}}}
    … …  
    4949{{{json
    5050[
    51 { "user_id": "8", "username": "mikiYoga", "email": "miki@example.com
    52 ", "spend_packages": "45.00", "spend_merch": "12.00", "total_spend": "57.00", "spend_rank": "1" },
    53 { "user_id": "13", "username": "bojan", "email": "bojan@example.com
    54 ", "spend_packages": "0", "spend_merch": "30.00", "total_spend": "30.00", "spend_rank": "2" },
    55 { "user_id": "12", "username": "ana", "email": "ana@example.com
    56 ", "spend_packages": "25.00", "spend_merch": "0", "total_spend": "25.00", "spend_rank": "3" },
    57 { "user_id": "9", "username": "davidG", "email": "david@example.com
    58 ", "spend_packages": "0", "spend_merch": "0", "total_spend": "0", "spend_rank": "4" },
    59 { "user_id": "10", "username": "proba1", "email": "proba1@gmail.com
    60 ", "spend_packages": "0", "spend_merch": "0", "total_spend": "0", "spend_rank": "4" },
    61 { "user_id": "14", "username": "ciro", "email": "ciro@example.com
    62 ", "spend_packages": "0", "spend_merch": "0", "total_spend": "0", "spend_rank": "4" }
     51  { "user_id": "8",  "username": "mikiYoga", "email": "miki@example.com ",  "spend_packages": "45.00", "spend_merch": "12.00", "total_spend": "57.00", "spend_rank": "1" },
     52  { "user_id": "13", "username": "bojan",     "email": "bojan@example.com ", "spend_packages": "0",     "spend_merch": "30.00", "total_spend": "30.00", "spend_rank": "2" },
     53  { "user_id": "12", "username": "ana",       "email": "ana@example.com ",   "spend_packages": "25.00", "spend_merch": "0",     "total_spend": "25.00", "spend_rank": "3" },
     54  { "user_id": "9",  "username": "davidG",    "email": "david@example.com ", "spend_packages": "0",     "spend_merch": "0",     "total_spend": "0",     "spend_rank": "4" },
     55  { "user_id": "10", "username": "proba1",    "email": "proba1@gmail.com ",  "spend_packages": "0",     "spend_merch": "0",     "total_spend": "0",     "spend_rank": "4" },
     56  { "user_id": "14", "username": "ciro",      "email": "ciro@example.com ",  "spend_packages": "0",     "spend_merch": "0",     "total_spend": "0",     "spend_rank": "4" }
    6357]
    6458}}}
    … …  
    7165{{{
    7266SELECT
    73 *,
    74 DENSE_RANK() OVER (PARTITION BY date ORDER BY utilization_pct DESC NULLS LAST) AS daily_rank
     67  *,
     68  DENSE_RANK() OVER (PARTITION BY date ORDER BY utilization_pct DESC NULLS LAST) AS daily_rank
    7569FROM vw_class_utilization
    7670ORDER BY date, start_time, class_id;
    … …  
    8074{{{
    8175γ_{class_id; COUNT(user_id)→booked}(Class ⟕ User_Booked_Class)
    82 
    8376калкулација utilization_pct = booked / capacity
    8477}}}
    … …  
    8780{{{json
    8881[
    89 { "class_id": "5", "date": "2025-06-09T22:00:00.000Z", "start_time": "08:00:00", "end_time": "09:00:00", "location": "Studio A", "capacity": 20, "booked": "1", "utilization_pct": "5.00", "instructor_id": "5", "daily_rank": "2" },
    90 { "class_id": "6", "date": "2025-06-09T22:00:00.000Z", "start_time": "09:30:00", "end_time": "10:30:00", "location": "Studio B", "capacity": 15, "booked": "1", "utilization_pct": "6.67", "instructor_id": "6", "daily_rank": "1" },
    91 { "class_id": "7", "date": "2025-09-26T22:00:00.000Z", "start_time": "18:00:00", "end_time": "19:00:00", "location": "Studio A", "capacity": 2, "booked": "1", "utilization_pct": "50.00", "instructor_id": "7", "daily_rank": "1" }
     82  { "class_id": "5", "date": "2025-06-09T22:00:00.000Z", "start_time": "08:00:00", "end_time": "09:00:00", "location": "Studio A", "capacity": 20, "booked": "1", "utilization_pct": "5.00", "instructor_id": "5", "daily_rank": "2" },
     83  { "class_id": "6", "date": "2025-06-09T22:00:00.000Z", "start_time": "09:30:00", "end_time": "10:30:00", "location": "Studio B", "capacity": 15, "booked": "1", "utilization_pct": "6.67", "instructor_id": "6", "daily_rank": "1" },
     84  { "class_id": "7", "date": "2025-09-26T22:00:00.000Z", "start_time": "18:00:00", "end_time": "19:00:00", "location": "Studio A", "capacity": 2,  "booked": "1", "utilization_pct": "50.00","instructor_id": "7", "daily_rank": "1" }
    9285]
    9386}}}
    … …  
    10093{{{
    10194SELECT
    102 training_id, training_name, month, num_bookings,
    103 RANK() OVER (PARTITION BY month ORDER BY num_bookings DESC NULLS LAST) AS rank_in_month
     95  training_id, training_name, month, num_bookings,
     96  RANK() OVER (PARTITION BY month ORDER BY num_bookings DESC NULLS LAST) AS rank_in_month
    10497FROM vw_training_pop_monthly
    10598ORDER BY month DESC, rank_in_month, training_name;
    … …  
    115108{{{json
    116109[
    117 { "training_id": "7", "training_name": "Vinyasa", "month": "2025-08-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
    118 { "training_id": "8", "training_name": "Yin", "month": "2025-08-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
    119 { "training_id": "6", "training_name": "Hatha Basics", "month": "2025-05-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
    120 { "training_id": "5", "training_name": "Vinyasa Flow", "month": "2025-05-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" }
     110  { "training_id": "7", "training_name": "Vinyasa",      "month": "2025-08-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
     111  { "training_id": "8", "training_name": "Yin",          "month": "2025-08-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
     112  { "training_id": "6", "training_name": "Hatha Basics", "month": "2025-05-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" },
     113  { "training_id": "5", "training_name": "Vinyasa Flow", "month": "2025-05-31T22:00:00.000Z", "num_bookings": "1", "rank_in_month": "1" }
    121114]
    122115}}}
    … …  
    128121}}}
    129122Датотеки:
    130 
    131 backend/proofs/events_explain_forced.json
    132 
    133 backend/proofs/class_bookings_explain_forced.json
     123- backend/proofs/events_explain_forced.json
     124- backend/proofs/class_bookings_explain_forced.json
    134125
    135126=== A) Настани по датум/време – составен индекс (date,time) ===
    136 Датотека: backend/proofs/events_explain_forced.json
    137127Клучен извадок што докажува Index Scan преку idx_event_date_time:
    138128{{{json
    139129{
    140 "Node Type": "Index Scan",
    141 "Index Name": "idx_event_date_time",
    142 "Relation Name": "Event",
    143 "Alias": "Event",
    144 "Index Cond": "(date >= CURRENT_DATE)"
     130  "Node Type": "Index Scan",
     131  "Index Name": "idx_event_date_time",
     132  "Relation Name": "Event",
     133  "Alias": "Event",
     134  "Index Cond": "(date >= CURRENT_DATE)"
    145135}
    146136}}}
    147137
    148138=== B) Резервации по час – два индекса ===
    149 Датотека: backend/proofs/class_bookings_explain_forced.json
    150 
    151139Индекс врз Class(date,start_time): idx_class_date_time:
    152140{{{json
    153141{
    154 "Node Type": "Index Scan",
    155 "Index Name": "idx_class_date_time",
    156 "Relation Name": "Class",
    157 "Alias": "c",
    158 "Index Cond": "(date >= CURRENT_DATE)"
     142  "Node Type": "Index Scan",
     143  "Index Name": "idx_class_date_time",
     144  "Relation Name": "Class",
     145  "Alias": "c",
     146  "Index Cond": "(date >= CURRENT_DATE)"
    159147}
    160148}}}
    … …  
    163151{{{json
    164152{
    165 "Node Type": "Index Scan",
    166 "Index Name": "idx_ubc_class",
    167 "Relation Name": "User_Booked_Class",
    168 "Alias": "ubc",
    169 "Index Cond": "(class_id = c.class_id)"
     153  "Node Type": "Index Scan",
     154  "Index Name": "idx_ubc_class",
     155  "Relation Name": "User_Booked_Class",
     156  "Alias": "ubc",
     157  "Index Cond": "(class_id = c.class_id)"
    170158}
    171159}}}
    … …  
    193181Објаснување:
    194182Повикот book_class() се извршува во транскација. BEFORE INSERT тригерот блокира преполнување (ако нема слободни места, CLASS_FULL), а AFTER INSERT/DELETE тригерите го одржуваат seats_available. Излезот погоре докажува: успешна резервација, потоа две одбиени резервации (полн час), и на крај бришење што ја зголемува променливата seats_available за +1.
    195 
    196 Сите views, тригери, функцијии и индекси се применети од backend/sql/ и се повторно-извршливи. Доказите се архивирани во backend/proofs/*.json