برنامه نویسی/کاتا برنامه نویسی/مباحث مهندسی نرم افزار

Counting overlapping events

۱۴۰۰-۰۵-۱۱۱۴۰۰-۰۵-۱۱ - توسط sabzechian

Counting overlapping events

مساله:

وظیفه شما ایجاد یک پرس و جو SQL است که حداکثر تعداد استفاده همزمان از یک سرویس را برمی گرداند. هر استفاده(“بازدید”) با زمان ورود و خروج در جدول “بازدیدها” به شرح زیر ثبت می شود:

id          primary key
entry_time  timestamp of visit start
exit_time   timestamp of visit end

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

when_happened  earliest timestamp when there were visits_count concurrent visits
visits_count   maximum count of overlapping visits

Your task is to create a SQL query which returns the maximum number of simultaneous uses of a service. Each usage (“visit”) is logged with its entry and exit timestamps in a “visits” table structured as follows:

id          primary key
entry_time  timestamp of visit start
exit_time   timestamp of visit end

A visit starts at entry time and ends at exit time. At exactly exit time the visit is considered to have already finished. The visits table always contains at least one entry. Your query should return a single row, containing the following columns:

when_happened  earliest timestamp when there were visits_count concurrent visits
visits_count   maximum count of overlapping visits

select v1.entry_time when_happened, count(*) visits_count 
  from visits v1 
  join visits v2 on v1.entry_time >= v2.entry_time and v1.entry_time < v2.exit_time
 group by v1.id
 order by visits_count desc
 limit 1

SELECT COUNT(*) AS visits_count, v1.entry_time AS when_happened
  FROM visits v1 JOIN visits v2 ON (v1.entry_time >= v2.entry_time and v1.entry_time < v2.exit_time)
  GROUP BY v1.id
  ORDER BY visits_count DESC, when_happened ASC LIMIT 1

WITH t as (
    SELECT entry_time as when_happened, 1 as v FROM visits
    UNION ALL
    SELECT exit_time as when_happened, -1 as v FROM visits
  )
SELECT 
  when_happened,
  SUM(v) OVER (ORDER BY when_happened) AS visits_count
  FROM t  
ORDER BY visits_count DESC
LIMIT 1;

select
  entry_time as when_happened,
  (
    select
      count(id)
    from visits v1
    where v0.entry_time < v1.exit_time and 
      v0.entry_time >= v1.entry_time
  ) as visits_count
from visits v0
order by visits_count desc
limit 1

with d as (select * from visits)

select
time when_happened,
cast(sum(visit) over (order by time asc) as integer) visits_count
from
(select
entry_time as time,
SUM(1) as visit
from d
group by
time
UNION
select
exit_time as time,
sum(-1) as visit
from d
group by
time) as data
group by
when_happened,
visit
order by
visits_count desc,
when_happened asc
limit 1

درباره sabzechian

نمایش همه مطالب توسط sabzechian →

دیدگاهتان را بنویسید لغو پاسخ

ببخشید، برای نوشتن دیدگاه باید وارد بشوید