SQL- שאילתת עומסים לפי שעות

[מנצפך]

יש לי טבלה שמכילה זמן שהות באתר.
היא כוללת זמן כניסה וזמן יציאה.
אני מעוניין בשאילתה שתוכל לתת לי בכל רוזולוציה שאבחר, מהם העומסים בכל שעות היממה.
המטרה הסופית היא להראות גרף של עומסים לפי שעות.
נניח אם נבחר רוזולוציה של שעה. ויש רשומת כניסה בשעה 08:30 ויציאה ב 09:10
היא צריכה להופיע בעמודה של שעה 9:00
אם נבחר רוזולוציה של 15 דקות, היא תופיע ב: 8:30 8:45 9:00
מה הדרך הנכונה לעשות זאת ב SQL?

חשבתי לעשות פשוט שאילתה שתעשה COUNT לפי תנאי.
ונעשה לכל נקודה רצויה בזמן COUNT עם תנאי משלו. אבל זה די מסורבל.

[avi rz]

@מנצפך
אולי

ליצור טבלה, עם כל הנקודות, משהו כמו זה:

00:00:00
00:15:00
00:30:00

שאילתה עם 2 הטבלאות (הנקודות זמן + כניסות)

לעשות תנאי של כניסה נמוכה או שווה לנקודה ויציאה גבוהה או שווה לנקודה.
ואז תקבל הכפלה של הכניסות לפי הנקודות שהשהות שלו כוללת.
ואז Count פשוט לפי הנקודת זמן.
נ.ב. יכול להיות שלזה התכוונת ולא הבנתי נכון.
ניסית להסביר את עצמי כמה שיותר ברור
יכול להיות שזה שאילתה כבדה - עוד לא התנסתי בזה.

מצו' דוגמא באקסס.
דוגמת שליפת עומסים.accdb

[dovid]

זה MSSQL או MYSQL?

[מנצפך]

@dovid
לא זה ולא זה.
Postgresql

[מנצפך]

@avi-rz
זה רעיון יפה.
אבל זה יפספס תנועות שנכנסו ויצאו בין הנקודות.
(נניח כניסה ב 1:31 ויציאה ב 1:35. לא יכלל בנקודה של 1:30 ולא של 1:45).
מה שצריך לעשות זה חלונות זמן. ולא נקודות. כלומר שכל נקודה תכיל זמן כניסה וזמן יציאה. (נניח תחילת נקודה: 1:30 סוף נקודה 1:45)
ואז בשאילתה דומה נשלוף:
את כל הרשומות שזמן היציאה או זמן הכניסה היו בין תחילת לסיום הנקודה.
וכן רשומות שזמן הכניסה קטן מזמן ההתחלה וזמן היציאה גדול מזמן הסיום

[dovid]

@מנצפך אמר בSQL- שאילתת עומסים לפי שעות:

Postgresql

אני לא מכיר וגם לא יכול לעשות טסטים.
אני חושב שהכיוון שלך צריך להיות ככה: אתה הרי רוצה ששורה מסויימת תופיע בהרבה שורות סיכום (כלומר פעילות שהתחילה ב7 ונגמרה ב9, תופיע בסה"כ של שורות שעות 7 ו8, ובמקרים של סיכום לרבעי שעה היא תיספר 7 פעמים).
לכן אתה צריך לעבוד עם JOIN בין רשימת הנקודות זמן (אותם תוכל לייצר ע"י פוקנציות של Postgresql או פשוט לשלוח מהקליינט את אוסף הנקודות ליצירת רשימה), לבין טבלת הפעילויות. הJOIN יהיה התנאי של האם הנקודת זמן בין ההתחלה לסיום.
את הJOIN הזה אתה מקבץ לפי נקודת זמן וסופר ככה את השורות.

[nigun]

@מנצפך
אולי כדאי להשתמש בTSDB
בדיוק אתמול ראיתי אחד שמבוסס PostgreSQL
https://www.timescale.com
אם אתה לא חייב SQL אתה יכול לבדוק גם את InfluxDB שיותר ותיק, ונותן ממשקים בילד-אין, ואולי עוד פיצ'רים, לא בדקתי לעומק מה ההבדלים.

[מנצפך]

@nigun
מה המטרה של TSDB?

[nigun]

@מנצפך
אני לא מכיר את הרעיון לעומק, ומה בדיוק ההבדלים בינו לבין מסד נתונים רגיל, אבל הנה כמה דוגמאות (לפי מה שהבנתי מהתיעוד)

• אתה רוצה להציג כמה כניסות יש לך לאתר בשניה/דקה/שעה/יום, אז בכל כניסה כותב למסד נתונים את כל הפרטים של כניסה + שעה, ואתה יכול לבצע שאילתות לפי חלון זמן בקלות, ובInfluxDB לקבל את זה באופן אוטומטי לתוך גרף יפה.
• יש לך הרבה סנסורים שמודדים את הטמפרטורה, והם שולחים כל דקה את המדידה למסד נתונים ואתה יכול ליצור גרפים יפים ע"פ המיקום,ממוצע וכו' לפי חלון זמן.
• כנ"ל על סטיסטיקות של מחירים,משאבים, וכל מידע שמשתנה עם הזמן, ואתה רוצה למדוד אותו ביחס לזמן, לא מצאתי פונקציה שמודדת ע"פ כניסה/יציאה אבל מאמין שאפשרי.

[ארכיטקט]

@מנצפך SQL לא יודע לעשות את זה טוב, יש כלים מיוחדים לכך מעולם ה BI, סוגים של גרפים וכל מיני תצוגות שמתמחות בדברים האלו, יש גם בחינם, ויש גם כאלו של HTML וג'אווה סקריפט שיודעים לאכול ג'ייסון ולתת לך כל מיני "מפות חום" וכדומה.
תחפש את הבעיה שלך באנגלית ובצירוף המילה Heat map ותראה הרבה עזרה בנושא.