00:00
hello everyone and thank you for joining us we have an intersting web on our planned and we'll get started in about one minute.
01:02
hello everyone and thank you for joining us we have a a very special webenare planned. today ay this is if we sad thirty one in our graph go the series tit's hard to believe is now
01:17
his that many of the series and a we try to make these as as informative as possible. this one is called gc quote writing best practices. and that's part one of a series.
01:32
not quite sure how many they will be in the series but i would think at least three and part one this episode is the first and it that appropriately is titled thinking can she sequel
01:47
i'm david ronald i'm director of product marketing at tage. an my background is shown here a bachelors in physics la masters in physics and i've worked in a number of technical fields with about t
02:02
over eighteen years experienced in the the tech industry much more importantly however i am joining at today by my very esteemed call. shing you chang who is our director customers solutions and
02:17
i was one of the co authors of gciquol. the graph queery language that we use here a taigagraph and and so it's very appropriate for him to be presenting today.
02:32
there is no other person who is more capable. off off eg. 're giving a very informative wabanar. and addressing questions you have about gciqual. and he has developed solutions for
02:47
en many companies not just forth and fifty 'about you know big and small companies globally. he joined tigerath in twenty fourteen so he has over five years experienced i think he's got a t five years and eight months.
03:02
interesting thing about shing yu which i'm not sure if he wants me to share for which i will do so anyway is that for his bachelor's degree. he had he studied. computer science and also japanese which i think is a very ninteresting combination and it shows the breadth of his curiosity.
03:23
before we get started three housekeeping items to cover we have muted your forne lines. but that's not an indication that we don't want to hear from you quite the opposite. we would love to
03:39
your questions i encourage you to submit them at any time its throughout the web banar and you can do that by clicking on the qne button in the menu this way banar is being recorded and we'll be uploaded to our website shortly and shortly means
03:54
typically two to three hours. and so if you're eager to review it i plan on doing that later today certainly tomorrow an and we will in addition to uploading the web and art to our website we will also se email
04:09
you a link to the archive along with. the slide and any other the relevant materials that shing yu wants to include in that email last but not least. if you have a question
04:24
with zom please contact either of us either myself or our ishing you and we'll do our very best to resolve them as quickly as possible.
and with that i 'm going to pass control off the webanar to shing you.
04:38
okay cough thank you davis for for the great introductions so that actually means even if someone as graduated from foreign language school they can still write good. good physical queries so let me share my screen so
mmmm.
04:56
so these will be today's agenda. so this is a first episode of the new sere on the current writing best of practices. so in the future episode we will also cover other topics like how to
man optimal data structure optimal accumulator structures.
and how to write memory saving quiries.
05:20
as well as
how to parallelize the query logic?
okay so this is the first time.
05:28
episode we will start simple but we will review the physics.
first.
and then i'll talk about the thinking steps of writing a query what to think about what are the questions to ask. when you start to write a this equal crime. then i will apply
05:47
practice those steps by giving three quil writing examples. okay then i will do like a live query writing to to practice those thinking steps
so in this tutorial wwe assume you already have some sisical query writing background have wels still like
06:06
i like to review the physics real quick. so if you are interested in more videos about this zico and it is absolutely recommended to watch this series of au video that is a un select statement.
06:22
okay we have a whole list of videos including the the demo the current writing demo and the very detailed introduction of each component of the select statlament this is very practical and useful.
okay and then let's get started.
06:39
so let's start with reviewing the physics. and so when we write a query usually a sisical query has two sessions. on the top we defined a query. name and the parameter. names right and in the corete body we have two sessions the first desession we define how the data structures.
06:59
so in this ion second session we defined other querry tre virtual logics so they cannot overlap. so we have to define all the data structure we need to use in the beginning and then we we write the querys say where we do we start from and then are as the controversial logics.
07:16
okay and so what does zisico do? so easily speaking it's nothing but you start from one set of ortex.
and then youe threw some selected edges originated from the starting set and then out reverse to another set of vertex.
'so it's like this you're always starting from somewhere
07:41
an for example you want to say okay i want to get all the patient has a claim belongs to the doctor of a certain specialty
right in this case.
we always have a place to start with.
07:55
and he then.
we select some edges and then we treverse to another site of vertex. and then we select some edges which refers to another set of vertex. just to repeat this again and again to hope from one place to another on the graph
so atthat is what thisical do.
08:14
what what this col do right is just as simple like that just the hop two different places on the graph right?
unknowing that so we know that tissico always haves to start from somewhere. so that means we need to in the query the first distin
we want to do
08:37
for the traversal is wwe need to define the starting point.
right so this is how we do it.
so if we look at the quarry here it takes the input what a type the input the parameter and then
08:51
we created
a vortex set variable named start set
out of this input vertex right so this will be your starting point of this query.
09:04
so here stars set is a variable
make.
it is a variable name of vertex set verbal. so here what accept variable? hit means it is a set of vortex. t is a set of vertex that you can with a select statement you can start from that set of bars.
09:22
what i said and it is also word hike said variable is also the outcome of a select statement right?
so in this case the vortex that starts out only contains one single vertex that is your input parameter.
09:38
okay and the this is always the recommended way to start your tribrota logic white because i see a lot of disicol writer they would like start from all what exists under that type and define the one once id equals to something but that is really slow because it scans over all the wor all right and this is the most
09:58
this is the fastest way to start from a single vortex and this is a recommended way.
okay and the alternatively we can also start from .
a set of vertex right? by taking a set of vertex as a parameter then create the vertex set variable out of it. but later i can just start from it.
10:21
or we can start from
all the vert exis belongs to a certain type say. i want to start from how the collaying vortex is. or i can start from all the vertexes in the graph.
okay so this is how we define the starting point of your traversial logic.
10:41
it can start from a vertex from a sadal vertex from a type or on wert axis.
and once we have the starting point now we can define the select statement originating from that starting point.
right we can select some edges and it revers to another set of vertex.
11:01
and this is a almost the hollow word of a select statement.
you can see in the first line line one we defined the start
this is the vertex set variable with the value of the input vertex right?
11:16
and then starting from it we selected ou the reverse associated edge.
and then which refers to claims so that is given the input input patient like that is this vertex.
actually worse i'm doing a pattern matching something like that right. the pattern is
11:36
starting from the start what hack said
and select all the edges attached to it.
and then find all the target vortexes under the type clan so that is what this lac statement does.
11:51
right so if you look at the chart
starting from the infant patient select all these tree associated edges.
and then
12:00
we say here you can see we selected t. so what is there t is the alies of your target vertex?
and they are targeted vertexes are these three vertexes on the right hand side of the edges.
okay. so that means since i have selected t as the result of selectement select the statement
12:22
then that means the outcome of this life statement will become this seam bartexes.
so therefore we assign the outcome the return value of this select statment. to a new wortex set variable callant. in think in this case the clan will contain three vertexes that that is clan a clan b and a clane c.
12:45
okay.
so that is how we writed them. it's the simplest just select statement and what or what is the meaning of each word in this statement
12:59
okay so but we can actually add a lot more to the select statement. so this is the backbone of the select statement. so other than what we already have like from and select?
we already have select them from here we can also add a wear cloud to to adda filtering on the edges.
13:19
alright we can do a que to define the logic you want to behave. on all the edges in parallel. and the poster cum we can define all the logics you want to behave on all the vertexes
13:32
and i mean by all the vertexes i mean all the vertexes you traversed in this hop okay not like globally all the vertexes.
and the having class is for applying some filtering logic to the vertexes being selected.
13:49
and the in the end you can other the other other than based on a certain value attributes or accumulator. and to choose the top k result in the vertex set variable.
14:02
okay so that's everything we could have in the select statement. on top of the hollow word version. cand so if you are interested you can look at the series of disico lecture video. toto see the details of each. each clouse in the select statement.
14:22
okay and then accumulators so as we know like a tiger graph execute is logic in then mpp way. at that is massive parallel processing. and so a lot of calculations the happens in parallel.
then in this case we need a special variable to handle this
14:45
like like when you want to assign value in a parallel manner right. in this example
like we are sending value one one two from three locations that is three edges be de db and dc from all these three edges edges we are sending three different values to the same variables.
15:09
then that means we need to have a way we have to have a mechanism to aggregate the value without a conflict.
alright so what we do is
it's the first of face.
15:21
we store all the values collected into a temporary bucket
okay and then the next phase once we received the all the messages we aggregate them into a new value of the accumulator. and then since then the new value of the accumulator will become accessible. if you are familiar
15:41
it's hadu for other big data platforms you know this is actually a map reduced.
15:48
process right it takes two phases. also to assign the value to accumulater. so the phased one is receiving the messages the message message is received. we'll be temporarily put to a bucket that belongs to the accumulator.
16:03
phase two the cumulator will aggregate the message it received based on this accumulator type. weill come to accumulator type later oka and the aggregated value what becomes accumulator's value and since then this value become accessible.
okay.
16:19
and this s analogy of accumulator it's like just like for example in the classroom there is a teacher.
the teacher wants to collect all the papers from al the students and the calculates an average score
okay and in this case
16:37
the teacher is the accumulator.
right and the students are the vertex and the edges.
and the test paper is the messages sent to the accumulator. al right and then the average score will be the final value of the accumulator. because it just works in a way how accumulative does right?
16:58
like in the phase one the teacher would collect all the
17:01
test papers from the students. that's just the phase one of the accumulator right receive all the al the values and store them in temperate the bucket. and then phase two the teacher would grade those test papers and then calculate every score of the class.
17:17
and that's the second phase accumulator it aggregates all these values into a final value of the accumulator.
okay.
so that's and we have for an accumulator we can define it in different ways.
17:33
on we have local accumulators and the global accumulators. so for local accumulator it is defined on every each vertex
we not average works literally it's like the
17:47
is it is defined the all the vertexes ou reverse. nokay only the one you touched so we will define like some a cume at a so it's the here. single adzines means this is a local accumulator or a vertex attached accumulator you find that on all the vertex is ove the worsed
18:06
and on the other hand we have global accumulator which is defined outside the graph in the global memory of this query.
and you can see this start with two add signs means it's a global accumulator so you see this one is outside the graph.
okay.
18:25
so that's the asomething store. are visible to all and hes something about your graph. right and then usually a vortex accumulator is something about your vertex
okay and for accumulators we also have different accumulator type
so
18:46
the type of the accumulator that decides how we aggregate. the value remember we mentioned that first the step we we collect the value of in the buckets right and then the type of different type of accumulator
19:00
aggregates the value in a different manner. like for the first one if for some a que it has al the value two. and they received a new value one thirty five.
and then
when we try to aggregate the older value and the new value we will take a sum of all these values that is elaborite.
19:20
right and for maxicum when we aggregate all these values we take the maximum that is five.
and the for men of hume we take the minimum one. and the average of cume we get the average number that is
two point seventy five.
19:35
right so other than these all these scholar types we we also have continual type of accumulators. that is if we're a set. it then maintains a collection of unique elements without another. right and all they are the unique elements. so
19:50
if het has older value two in the set then after receiving new values one three three five.
then it will only keep unique values so it only keep one three
right in the end the new value of this set will be two one three five.
20:05
and for hlist it keeps duplicating values but in an order. right so if you have older value two in the list and we see one five three three.
this is his a new list of elements in your list. kay similarly we also have a map as a key value store and hepa cum as a oter top k structure.
20:29
okay so it's all about how you aggregate the message you received in the bockets.
all rate and the dust accumulators
20:40
and then you can populate accumulators from the vortex and the engines just like it is. no i'm sending like for this example we are sending one four ten. to the local variable defined on this vertex. right and than this time i'm sending the value to the global. variable so we can do that in the
20:59
if ye go back here we can populate the accumulators from a cume clouds and the postal cubes.
if youd do it from a cum clouds you are doing it from edges hopefully and for post a cum you are doing that from vo por taxes.
okay.
21:19
and
now let's move to the thinking steps.
well sometimes when we try to write a query we know all the knowledges we know the classes an and the accumulators.
21:34
on sometimes we need to ask the questions to start with we need to know where what to think about first and where to start with.
right okay so
these are the questions to ask to help you start with writing the query.
21:52
so the first es step we need to define a designed the traversial path
the questions to ask is
where to start from?
22:01
what are the steps how many hopes do you want to traverse and what are the edge types you want to traverse each step
right and then once we have designed the triversial plan we can think about okay what about the data structure
what what do i need to be in the without
22:20
and so in order to get that without whatting.
his the where is the youthful needed?
in your triversal pass
22:28
right where are they where are the needed the information?
and in order to gather the information together what accumulateer
tro we use?
22:38
right and then we can getwe can get hour accumulator device. and the once we get the accumulator defined. we need to think about okay how to populate accumulators. all right how do we gather the information to the right place?
22:54
and then we can finally produce the final result and print it out right so that is so these four steps
and all these questions is the thinking flow when we write a queries
okay.
23:06
and let's take three examples and each example we will like go through all this question. to to think clear about how we should write this query.
okay.
kay so the first example is to order the specialties in descending order by the number of claims.
23:29
okay so older the specialties because each specialty is
well have a lot of claims for of that doctor of that specialty we want to order them in the descending order based on the number of claims
okay so here on the right hand side is the schema here is where specialty is.
23:47
specially has a sub specialty.
subscriptly has some doctors belongs ton the subspecially. and he each doctor have a lot of claims and each claim. also is a sache adent to a patient. that's how the schema looks like right. so we want to try to answer all these questions based on this schema.
24:07
okay so first the question where do we start from?
okay so let's look at the schema so we start from spaciolti then star start from specialty we can traverse all the way to the claims.
and then maybe we can do a count that's one possibility but that's one possibility.
24:28
right how about what if we start from claim?
we can also start from claim. she wors to prescriber and then subspecialty and then back to specialty. and so these to both of them are possibilities however when we carefully evaluated the both
i think
24:48
the easier way would be starting from the calline.
right? okay.
an if we start from the claim let's see. well what are the steps okay so if we start from the claine it will be from calane to prescriber? and then to subspecialty and then to specialty.
25:08
right.
and what are the edges to be used and i think that's very straightforward some meat of course
25:15
and then subservation is to prescriber and then especially to subscribes for it it's very obvious to answer this question right okay now we get this. carry it out a i'll come back to why we don't start from specialty later.
25:30
the nest question is in order to choose under defined the accumulators what is the pennal result
okay. so since the question was asked is older the specialties in the descent and order by a number of claims
right so what we need is the number of claims on each specialty
25:54
right so we need a number of claims.
so data means
okay so the frannel
26:00
result would be special a specialty is ordered by a number of claims we need a number of la that's where your need the result but that is also very obvious. a and then the question is what is the info where is the info needed in the final result?
right?
26:16
of course because we want a number of claims of course the value is .
i'll claim where lex this.
right because we need to count a number of bit okay and he ask question what accumulator do we use?
26:30
and now since we need to count a number of claims so it looks to me if we scan through all the accumulated types. looks to me that is same a cun because this one counts the sumit
some number of values right we want to count the number of a claims so since wwe need the summa cum in this case.
26:51
now the others for sure .
yeah so in this case yeah i think we need to define a
sound like you.
27:01
for this case.
right?
and the last question how do we gather the info to the right place because right now t
27:10
the unful meet is unclaimed. in then we want to count the number with the summa cum harvard. in the end we want to order the specialty by the number of claims so we need to pass
it's a number all the way.
to specialty in order to get the count of claims
27:28
right?
so that is so the solution would be pass the number of claims from collim vortexes all the way
27:38
okay let's write a query based on the answer of this question questions okay? so now we already we have triadent out like what this the traversal path and the edges and let's just read the current based on that.
okay.
27:59
okay so we don't need parameters right because we are doing this for all the specialties and we also know okay look has a cheating sheet where do we start from? we want to start from claims right?
okay a let's just start from clans.
28:21
o kay and this is how we start from attack
and next the thing.
and this is troversal path i mean these are the edges that will be needed right? so four in this touterial we only write quaries in syntax won. because right now at this point
28:41
a syntex ruwan has all the features its supported and that at has best perforens. so let's stick with the syntax we want first and if we were interested in how to do a easy query writing. you can check the our document to learn o. where going sitext to me too okay? so okay so he these are the
29:00
ages we're going to use let's s
will be the jim them here.
okay so we start from that the first that wech revers to the prescribers right?
29:27
all right so this is a simple este.
a slack statement and now we get the pres weber and from prestore now we are going to sub specialty.
right?
29:43
and next time i'm using this edge
in the trip or is to a sad
hall right?
29:52
and just simply like that and now i'm going to strom sub specially i'm reversing to special te
30:06
it's is o and this is the third edge we are going to you on .
30:15
o kay oh
al's main is a little bit different
30:24
so thank you.
okay.
and that this is obvious triversial plan is simple like that right kay so now i have done this this this and let's go to the next.
30:36
okay. so
now we know that we need to define the summa cube right ookay let's define the summa cube here.
saying clane camp right?
30:51
so okay and we also know. we need to in order to populate this accumulator we need to passed the number of clains all the way from calans to the specialty
all right so now we have cleane numbers right here from the starting point.
then with a cube i can pass around the number.
31:15
right?
heay oh i think a new rons not be claim this is actually this is the prescriber
kay and then now let's pass the value all the way around to specialty. okay. so proscribe her
31:45
okay so what does this mean so press is the
alies of the targeted vertex and the targeted vertex is the prescriber vertex right that it means for every each
edge starting from clane to prescriber i will increment the accumulator defined on this prescriber by one.
32:08
okay pretty much
how depends on how many edges that is from claims to free screver. and then we will keep a number of the number of collains of each prescriber.
32:20
all right and now we have the number of claims. stored on a prescriber next to saying we are passing this. we just get a counter number of clans of each free scraper. now we are passing this value along to sub specialty.
32:41
sub retugies this is what we want to populate right because equals to the crop.
prescribers right we are passing the prescribers value. to the sub specialty as the next thing we're going to do the same right.
passing along all the way to the special you
33:16
alright and at this point we already get a count of claims stored on this the specialty the vertex of specialty vertexes right we already have a claim count there. and in order to produce the final result we want to
by asked ad.
ling count pleas se
33:41
and that's at the query righting is done that's our first example and the print the final result. .
34:14
n okay i see i received some questions i'll try to answer while it's compounding why do we calling and said well it's a single vertext.
well as i said containing only only a single vertex right how can it store the past of vertex from his source?
yes.
34:34
on we actually are from our grav algorithm elaborate you can find some shortest pass algorithms and from there you can and you can see that how we can store the past from the source.
okay.
and let's just for t run it.
34:50
right. so that's
if we check the
based on result output you can see now the specialties are already altered by its number of claims
35:01
right in the descending order.
okay.
aso now let's go to our next example and again. we will go over the same set of questions to figure out how should we write the query. okay.
35:19
example two is given the prescriber who are the top k other prescribers serving the most patient.
35:28
because the patient can raise the money many. prescribers right so that means if we want to take a prescriber as an input we want to see okay who are the other prescribers that shared the same? sattle patient and then we want to order them by the number of patients.
right okay st.
35:48
again where to start from
okay so this one seems to be obvious because
we are asking you a question for a specific input prescriber then i think hopefully we would just start from there right otherwise
36:05
do we start from al the patient no start from all the other prescribers that would be too expensive right?
or subspecialty not relevant so the answer is quite obvious.
36:17
what are the steps okay? so now? we want to get another settle prescribers that shares the same patient and hopefully we want to have the patient in the middle. right and what connects we have patient in the middle what connects between patient and prescriber claims.
36:37
the hopefully it's a four hop query starting from input prescriber to claines and then patient and then clans and the other prescribers.
all right so that's hour retroversial planned.
36:50
and the what edge should we use since we already figure out what it tex to treverse then the edge type seems to be very obvious as well.
right.
and then what do you want to be in the final result
37:04
okay so in the final result.
of course we want to have the prescribers who share the same patient and the number of patients.
right.
37:16
and
what is where is the infall needed for the final result because we are counting the number of common patients
nell i would have saying
37:30
the info is on the patient of what hack said.
right?
and then what accumulators should we use the here we have a
37:39
'we have a choice to make.
right shall we use because we are
ordering the top cape prescribers based on the number of component patients.
37:53
so intuitively thinking the common patient should be a number r
right?
a number o
38:01
of patients
how ever
if we really look at our treversial plan
38:08
between
between the patient and the other prescribers we have claims
so
38:19
since we have claims in the middle if one patient has multiple clains with another doctor
then we made a cut.
duplicate the count we may count the same patient multiple times if we use a summa cule
38:36
then if we don't use the summa cum so that means we want to count the distinct
remember a patient.
so what accumulator can help us of
38:50
right.
so twhat accumulator can help us to count the distinct number of patients.
i think that will be a set a cue because set a cume can keep unique elements in its container.
39:05
right so let's do catacum.
okay.
and then how do we populate the set of que because right now the patient's ides are here and the final results will be printed on prescribers.
39:19
right? so we need to pass to e patients with accides.
two the pre survivors
okay now i think we have stritten out other questions you need to think about and let's just to write the can.
39:53
okay.
by looking at the questions here where do we start from the input the prescriber
40:05
oh i know we need a in for the free squiber right?
40:15
you find a vortex type
40:23
okay this is where we start from and then loop come back.
where are the steps so these will be the steps and these are the edges we are going to tremorse.
okay and then let's a look at the schema over here is it easier. a then that's true for some mate queries
40:50
is are time when the ter summa to edge to the lines right that's the claims and then patient.
41:10
patient. right and the again which refers to other claims
41:28
right.
but.
here.
41:41
patient
41:57
oh it's not san is a sagiate
42:04
okay but here. since we want to get all the other claims now the a claims we already have from the input prescriber. right because here if we start from patient and they use this age will reverse back. to the claims of the starting point right so here let's to also define that
42:26
a bullying tape or a can
we prevented from revisiting the older barak right so we want to say okay for all these claims i mark them
the true.
42:44
and then say for the new climbs i want to filter them out
42:53
right and that these are the claims right and after getting the claims on the treverse
though the prescribers all the other prescribers right?
43:10
ron claims
for a miminute.
right so this is our retical plan
43:22
okay
and he next let's look at what accumulator do we use sato que this is what we have agreed.
right so let's define the set of cube.
43:36
cause we want to use the set a con to store the ide
of the patient right then let's mediate set a canolax.
pla patient said
43:49
alright and the next the question
we want to pause the patient pass the patient patient ideas
two the all the way to the prescribers right?
44:03
pass id to claims and then to prescribers
okay let's do that.
so where is the patients so here we got the patients right and here is where we start from the patient and that's the place we want to post the patient's id
44:19
okay than i say a come see hour at pattient's that cass equal to let's give you the alias
okay so the alias means the starting verdax right so if you don't if she has to use the name it is the vertex type. representing the vorax okay?
44:43
and now i am populating the set stored on the claim all the other claims.
whith this id.
and after hexecution of this class on the claim you will have a set of patient id and now i'm further passing this set of
45:00
ides to prescriber.
but i will pro populate
45:31
okay ande
right now we have a set.
settled patients start commentation. the setal common patient is stored on the all the other prescribers who shares the casa comments to patient right now we want to order them
45:53
size of the set
okay and then let's bring them out.
okay and that's that should be the quarry.
46:06
let me check the qna. what's the different between a can and ppost a cum okay that's a great question so
if you look at this select statement here.
46:17
okay line sixteen. we have a a cute acute means for all the edges starting from this patient patients site. connecting to the claims. we want to ask you to this logic
what is this logic?
the logic sending the ide of the patient
46:37
along the edge to the set defined on the claims.
so that after executing this line
the set the patients set on the claims will cale will store a list of patient did
46:51
okay so that so easily speaking logic after a cum clouds is executed on every each edge being selected.
and he the logic after the poster come escued on every vertex
being referred now i am referring to calae right that means for every each calan
47:12
we connect ate it by this edge we'll ask you this logic that is a mark.
the visit to the barival to chu.
okay and ten later i can use them for future in confesion.
47:23
okay.
let's try to run this query let me find the chris quare id first.
47:33
pree something okay?
47:49
al right.
no lack please startet
47:58
oh i news to chi chain stake the right type forty eight
and here is the top list right the top one is prescriber 29 has three common patients .
he is not altered
48:17
oh seems like altered by ascending haltered that's why. the top one has a list a common patients and the last one on the list. has the most
common patience that is a prescriber 23. so if you want to already like this any other you usually had this key word but now yeah. joest the bear with me.
48:38
okay and now let's move to the last a
querry and that is also the most complex one that is how we create as the referral edge.
how we ident detect the preferrel relationship between the doctors.
48:53
and then that's an insert a new act for that.
okay.
so what what does refer to men
49:00
as referral means if a patient has two claims
okay two claims with different doctors.
if one claim it is if the second claim is within the certain period of time after the first eclaim is very short. the two country is really very short.
49:22
and ehappens really soon between each other. and theyre they are from different prescribers and in mean the first prescriber. referred another priest weber. so we want to detect this pattern from the graph
49:36
okay so again blet's ask the same set oh i don't sere is all rady here let's ask the same sets of question where do we start from
since the question is given the prescriber so i think it totally makes sense to start from this impunt prescriber
so what are these steps?
49:58
again so this is also based on that we need to find the patients .
that has claimed with the input of prescriber. i had another claim after within a certain period of time after the first colling with the emble prescriber with other prescribers right?
so i think the triversal plan is just the same.
50:18
is the previous query we need to start from the impleted prescriber go to claims then patient thand claims and the prescribers.
right and then these are the edges will we'll be used in the fiber t this is a street board.
50:31
'm trake party is here. what is the final result? the final result will be the referral relationship between the presquarebers
as long as at they have the patient have the consecutive two claims one after another.
50:47
right so the result will be the referral edge between input hed prescribers and the referred e.
prescribers
okay so how do we identify the input prescriber and the referred prescriber where is the info needed for this final result?
51:04
then let's see look at the schema.
okay so we have patients here.
patients may have different climes.
51:12
and we want to make sure the claims are between a input prescriber and other prescribers so that means
the yuval lies in three places.
the claims of the improve prescriber.
51:25
the patient.
and the other claims of the patient.
right so it is in the three locations.
51:34
all right so than what accumulate her?
do we use?
how do we
51:43
how do we say okay to finger out okay if two claims is
he one is like one is after another.
right so ofthat means we need to have the dates we need the date time information of the previous claim.
52:05
okay. so that means let me try to annotate here a little bit.
52:19
so that means whill we
start from a prescriber and the we get all their claims right
and them from other claims
52:31
we got all the patients.
so here the psycon layer is patient.
52:39
and then which revers to other claims.
and the for the other claims we want to finger out one thing
it has wisin a certain period of time
52:54
easing a certain period of time
of the claims this patient had with the input prescriber.
its right.
53:02
but in order to fingure out his within a certain period of time we need the alter dates. from the previous clients with the input right? so that means we need to store multiple dates here.
i is a different card .
53:29
as hear okay so what is the structure to store multiple dates?
we can use set that this a possible answer because we can store a set of states.
right from the previous like those are clan dates with the input prescriber so this is the
53:48
i'm on it to prey square boot right?
and or we can use the list list there is a more light a weight head and then maybe let's just go with the list.
right that makes things easier.
54:01
okay so the answer to this question since we fingered we need to
54:11
we need the list ocule of the dates to be put
on the patient so that we can do further comparation with the claims of other prescribers right?
so how do we populate that?
54:25
yeah of course
since we have we need the list of days. defined on the patient then we want to pass the days of the claims of improves. prescriber to the patient vortex.
right
54:37
okay then that's all the questions we need to ask let's write this query.
okay so maybe
great
54:49
let's copy the last question quite a little bit because he is a very similar to
right.
in the mall.
55:06
okay and we don't need this set any more and also let's remove the logic populating the set r.
55:18
okay so this is our etra versial plan right now let's attach the accumulators to it as we have a grade we need to define a list the accum of data time
55:35
right and if we look at the schema its something like this right we need to
start from the input prescriber. revers to the claims and then populate the date of the claims to the patient.
right if you have multiple then you have a list.
55:57
that's what's going to happen right so we're going to populate the dates of the clients to the patient
and that will be this hop right. that's where what we do when we gen to the patients say okay
patients facing list.
56:26
all right now after executing this we have a list of dates on the patient.
lright and since now we have this list of data having built and when we reverse to the next hob to other claims we can compare the list
but where is the date of the new claim?
56:45
right let's do that.
56:51
say okay now i want to compare each
in that list
whor is the new
57:03
clime date.
right?
okay so when how do we do the comparation let's check some document?
57:15
let's say day to time day time functions that's what what i'm looking for.
57:27
okay and we have a
daytime deep right okay that's what we need. so we want to make sure the daytime deep between the
colaine dates are act you date
57:50
and the date in the list.
zero and m let's say
two weeks.
58:07
this number
fourteen.
twelve horver's fourteen this number right
58:20
it if this
it's qualed satisfied and that means this claim is a claim being referred by the input prescriber right then i want to keep this claim.
to the result how do we keep it in the result
58:36
i think we can use a ora hume right?
so that we can keep it
58:49
will just mark them with a poline value.
o great
59:16
i'll see you.
i want not keep the king
right.
59:20
okay.
and now we have the claims.
we we want to keep all the claims.
59:33
etcuals having this value true because when when it the qualifies i want to keep it such as true right and then i want to break the lobe
okay and in the end we have finally from al the claines we want to keep to the prescriber we want to insert the edge.
right.
59:56
you insert into a refer roll values
in good.
in for the prescriber
01:00:09
end the need to prescriber who who has referred
01:00:20
is zero.
okay for the attributes
01:00:31
olkay then we are good
01:00:37
on can what is the difference okay can we got a couple of slides in recording? po this presentation. oh for sure after this governor we will ensen you all the slides and everything.
01:01:04
ho yes cold as well
o kay and
this is the querry let's just try to relente what it's the saying
01:01:17
okay so has since we have three prescribers being referred by this invut and also we created the edge
and i just got another question say can we? practice this practiced this from the database. yes actually i'm currently using tagagram coloud incidents. you you can just go to tg cloud do. al.
01:01:44
and then you can start of for for fore instance and right now i'm using the a healthcare referral network starter gate later you can play with the two.
okay and so that's all my content so by asking this whole list of questions usually we can make make up
01:02:09
ake ourselves think really think through think clear like what to do when your red the query.
okay so maybe you can do more practice by a asking yourself these questions and following the four steps of writing a query. and the in future episodes i will have more of the ones in the top hake say a
01:02:32
about how to defined the optimize the data structure how to write memory save in quiries and as well as how to parallelize
the querry in order to get a better performance
okay and
01:02:47
david i think i'll hand it to you.
okay.
a great thanks.
01:02:54
can you stop sharing past me back control perfect thanks
01:03:05
okay so i think in the interests of time shing you that was an excellent presentation absolutely incredible i learned that a great job i think in the interest of time we'll just move forward and wrap up the
01:03:21
presentation i know there are a couple of questions that we haven't addressed yet but we will do that a follow up emails the post web and ask follow up emails so if there are more questions there's coat least a couple of ways for you to get those questions
01:03:36
addressed of course you can also reach out to shing you directly but we have our development forum. and we recommend that you join that forum it's a great way to you don't want to hear from us but her from other people a within the community who are a
01:03:51
our dealing with issues that might be very similar to yours and in that way that might be a more effective way for you to get answers to the questions.
01:04:00
an additionally every thursday at 11 am. well it's now a pdd pacific day like savings time. if you le 11 am pacific time we hold our developer office hours that's a lie. online session for anyone to submit a question that i have.
01:04:15
our subject matter experts address as shing you said in response to that question. we encourage you to try a cagraph cloud that is an excellent way probably the best way
01:04:30
gets started on a database and it's free it takes about six or seven or eight minutes only to get started. and there's a free tear. free and perpetuity and then there's additional if if you want to get access to additional startup hits.
01:04:45
tho the quantity of startucits you can see a credit card will give you twenty five dollars in credit which is typically you know ten hours or so for you to try other start up its as well. additionally its what point.
01:05:00
that if you do have one start a kit and within that free tier and you decided you want to switch you can always delete that start a kick now you will lose all of the data and any changes to the schema and so forth that you've made it that's another way for ou
01:05:15
and you using the platform for free so really there's no reason for you not to start using tiger rat cload you know right after we've finished this we at youre airliest convenience there's additional ways for you to get information they're listed here. yeah.
01:05:30
its of time i'm going to move forward now and just talk about our next couple of web an arts our next web an art will be next week.
01:05:39
tuesday which is worth noting our usual web and our schedule falls on every wednesday but our next one will be on tuesday march twenty fourth at eleven a.m. specific time and this is being hosted with our partner.
01:05:54
sperol and that will add even more and that will. even more the experience i think for everybody in the audience the topic is a round table. on fraud detection with graft and machine learning and if anyones to interested.
01:06:09
anybody is interested in the second episode of this series that shing use has just kicked off. an episode two tentatively is scheduled for april eighth so in about three weeks of my math is correct that will be on wednesday. year probably.
01:06:25
and that brings us to the end of the webenar i know we ran a few minutes over i appreciate everybody staying to the end we know you are very busy there's probably a lot of things you could have. done in this past or were were flattered.
01:06:40
and i really appreciate your choosing to spend the time with us. we hope that our enthusiasm for for graph been has rubbed off i was going to see contagious but that's probably not the right thing to say given our current a health and economic.
01:06:55
iss but we hope our enthusiasm has robbed off on you and we would love to see you at a future. one of these weapon arts ut for know that brings us to the end. thank you for joining us we hope you'e found this informative.