30854

MySql Select last row with 30 minutes difference in date

<h3>Question</h3>

This is a followup to this question MySql Select rows with 30 minutes difference in date, albeit similar in concept the solution needed might be different.

I have a MySql-8.0/MariaDb-10.4 table that contains a list of site visits of different visitors:

I want to create a query that returns the last visit of each visit session, where the session definition is where the CreatedAt date is 30 min or more from the previous visits.

So in my case, I should be returning row 7 (Id column), row 12 and row 13. Note also that a session can be more than 30 minutes, as long as each visit succeeds a previous visit with less than 30min.

The neat solution suggest by @EugenRieck was as follows:

SELECT late.* FROM activities AS late LEFT JOIN activities AS early ON late.VisitorId=early.VisitorId AND late.CreatedAt>early.CreatedAt AND late.CreatedAt<=DATE_ADD(early.CreatedAt, INTERVAL +30 MINUTE) WHERE early.Id IS NULL -- Maybe: AND late.VisitorId='26924c19-3cd1-411e-a771-5ebd6806fb27' -- Maybe: ORDER BY late.CreatedAt

It works great, but it works by returning the first visit in each visit session, not the last visit. I tried to modify to work as i wanted but with no luck. Please help.


<h3>Answer1:</h3>

This is a variant of gap-and-islands problem. But you can handle it using lead(). Just check if the next createdAt is over 30 minutes from the value in a given row. That is the last row for a session:

select a.* from (select a.*, lead(createdAt) over (partition by visitorid order by createdat) as next_ca from activities a ) a where next_ca > createdAt + interval 30 minute;

Usually, in this situation you would want the last row as well. You would get that with or next_ca is null.

来源:https://stackoverflow.com/questions/61443546/mysql-select-last-row-with-30-minutes-difference-in-date

Recommend

  • Count rows from different tables in join statement
  • Doesn't qsort() C library function work on linked lists?
  • OpenCL files fail to compile on OS X
  • Creating a custom control in Xamarin
  • Load more status from database
  • InternetExplorer.Application with -nomerge switch?
  • How to get real term_meta using ACF with custom fields on taxonomy terms (instead of wp_options)
  • API Gateway Encoding multipart/form-data
  • Using MPI_Send/Recv to handle chunk of multi-dim array in Fortran 90
  • Insert statement not working using execute(array()) of PDO Extension
  • How to smoothly connect two signals in matlab [closed]
  • React/Redux. Access state from other component
  • date changes on export kendoGrid
  • Get the UTM tags with Facebook Marketing API
  • SELECT on JSONField with Django
  • Reader monad - how does it conform to Monad interface?
  • Slice assignment to tensorflow variable
  • Excel File upload in asp.net using SqlBulkCopy
  • How to resolve this in PHPUnit where it is asking me to set KERNEL_DIR in my phpunit.xml?
  • Year over Year Stats from a Crossfilter Dataset
  • Tensorflow Dataset API restore Iterator after completing one epoch
  • Rotating Towards Path in OpenGL
  • C++ STL stack pop operation giving segmentation fault
  • Calculate time from document
  • Neo4j…how to get a visual representation of my data?
  • How to include associated objects using gon in Rails/jQuery
  • 'url' requires a non-empty first argument. The syntax changed in Django 1.5, see the docs
  • Amazon Elastick BeanStalk error: Failed to create the AWS Elastic Beanstalk application version
  • Stacked bar chart with continuous time-axis as x-axis
  • Bind selectedDates Aggregation for Calendar
  • Functions by reference or by variable, which to use when?
  • Bitrate JWplayer
  • XEP-0166: Jingle protocol implementation for voice/video chat in iOS
  • Call Microservice from another Microservice within Docker
  • Jersey serializes character value to ASCII equivalent numeric string
  • Firebase: How to read from external DB?
  • What does the “id” field in an Android “Google Play Music” broadcast intent correspond to?
  • Access to a Matlab gui from the web