topic Is there a way in Azure to compare data in one field? in Data Engineering

Is there a way in Azure to compare data in one field?

CBull — Tue, 15 Mar 2022 21:31:48 GMT

Is there a way to compare a time stamp within on field/column for an individual ID? For example, if I have two records for an ID and the time stamps are within 5 min of each other....I just want to keep the latest. But, for example, if they were an hour apart I would keep both records.

Re: Is there a way in Azure to compare data in one field?

merca — Wed, 23 Mar 2022 19:51:59 GMT

Windowing function can be what you need.

from pyspark.sql import functions as F
df.groupBy(F.window("event_time","5 minutes"))

Re: Is there a way in Azure to compare data in one field?

CBull — Wed, 23 Mar 2022 20:12:43 GMT

So, is this done something like this?

SELECT

r.patientmedicalrecordnumber,

r.callreceiveddatetime as date

FROM table r

LEFT OUTER JOIN table p

ON r.pageid = p.pageid

WHERE p.pagetype = 6

and cast(r.callreceiveddatetime as date) = current_date() - 1

df.groupBy (r.window("event_time","5 minutes"))

ORDER BY r.callreceiveddatetime

Re: Is there a way in Azure to compare data in one field?

merca — Thu, 24 Mar 2022 03:00:45 GMT

Since you are trying to do this in SQL, I hope someone else can write you the correct answer. The above example is for pyspark. You can check the SQL synax from Databricks documents