Hi all,

Novice MUMPer here. I am trying to query a large dataset using M and was wondering how to efficiently query a global.

For example, how does one filter for 10,000 of the most recent records (by Unix Time) which have a pattern of ^table(*,"A","C")? For a small list size, one could write some kind of $data/$order counting loop, but is that really the most efficient way to query something like this?

^table(1639615761233,"A","B")=1
^table(1639615761499,"A","C")=1
^table(1639615761761,"A","D")=0
^table(1639615762029,"A","E")=1
^table(1639615762298,"A","F")=1
^table(1639615762696,"A","G")=0
^table(1639615763508,"B","C")=0
^table(1639615763821,"B","D")=0
^table(1639615764099,"B","E")=0
^table(1639615764380,"B","F")=1
^table(1639615764640,"B","G")=1
^table(1639615768122,"D","C")=0
^table(1639615769781,"D","F")=1
^table(1639616032012,"A","B")=0
^table(1639616032294,"A","C")=0
^table(1639616032565,"A","D")=1
(...)

пятница, 17 декабря 2021 г. в 02:15:14 UTC+2, KM:
> Hi all,
>
> Novice MUMPer here. I am trying to query a large dataset using M and was wondering how to efficiently query a global.
>
> For example, how does one filter for 10,000 of the most recent records (by Unix Time) which have a pattern of ^table(*,"A","C")? For a small list size, one could write some kind of $data/$order counting loop, but is that really the most efficient way to query something like this?
>
> ^table(1639615761233,"A","B")=1
> ^table(1639615761499,"A","C")=1
> ^table(1639615761761,"A","D")=0
> ^table(1639615762029,"A","E")=1
> ^table(1639615762298,"A","F")=1
> ^table(1639615762696,"A","G")=0
> ^table(1639615763508,"B","C")=0
> ^table(1639615763821,"B","D")=0
> ^table(1639615764099,"B","E")=0
> ^table(1639615764380,"B","F")=1
> ^table(1639615764640,"B","G")=1
> ^table(1639615768122,"D","C")=0
> ^table(1639615769781,"D","F")=1
> ^table(1639616032012,"A","B")=0
> ^table(1639616032294,"A","C")=0
> ^table(1639616032565,"A","D")=1
> (...)

You can do something like this:

s (date,var1,var2)="",interval1="16396160....",interval2="16396161...." ; Your time intervals !!!
f s date=$o(^table(date)) q:date="" d
. f s var1=$o(^table(date,var1)) q:var1="" d
. . f s var2=$o(^table(date,var1,var2)) q:var2="" d
. . . i (interval1'="")&(interval2'="")&(date'<interval1)&(date'>interval2) d
. . . . s steps="Your next steps: ="_date
.. . . . w steps,!
..................

пятница, 17 декабря 2021 г. в 02:15:14 UTC+2, KM:
> Hi all,
>
> Novice MUMPer here. I am trying to query a large dataset using M and was wondering how to efficiently query a global.
>
> For example, how does one filter for 10,000 of the most recent records (by Unix Time) which have a pattern of ^table(*,"A","C")? For a small list size, one could write some kind of $data/$order counting loop, but is that really the most efficient way to query something like this?
>
> ^table(1639615761233,"A","B")=1
> ^table(1639615761499,"A","C")=1
> ^table(1639615761761,"A","D")=0
> ^table(1639615762029,"A","E")=1
> ^table(1639615762298,"A","F")=1
> ^table(1639615762696,"A","G")=0
> ^table(1639615763508,"B","C")=0
> ^table(1639615763821,"B","D")=0
> ^table(1639615764099,"B","E")=0
> ^table(1639615764380,"B","F")=1
> ^table(1639615764640,"B","G")=1
> ^table(1639615768122,"D","C")=0
> ^table(1639615769781,"D","F")=1
> ^table(1639616032012,"A","B")=0
> ^table(1639616032294,"A","C")=0
> ^table(1639616032565,"A","D")=1
> (...)
you can $order -1 and use the counter to read the last 10,000 records ...

s (date,var1,var2,cik)=""
f s date=$o(^table(date,-1)) q:date="" d
. f s var1=$o(^table(date,var1,-1)) q:var1="" d
. . f s var2=$o(^table(date,var1,var2,-1)) q:var2="" d
. . . s cik=cik+1 q:cik=10000 d
. . . . s steps="Your data: ="_date
. . . . w steps,!

To me, the most straightforward approach seems to be something like:

Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
. Set reference=$Query(@reference,-1) Quit:reference=""
. Quit:reference'["""A"",""C"")"
. Set count=count+1,result(reference)=1
. Quit
Quit

(please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)

On Friday, December 17, 2021 at 11:44:35 AM UTC-5, ed de moel wrote:
> To me, the most straightforward approach seems to be something like:
>
> Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
> . Set reference=$Query(@reference,-1) Quit:reference=""
> . Quit:reference'["""A"",""C"")"
> . Set count=count+1,result(reference)=1
> . Quit
> Quit
>
> (please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit com

On Friday, December 17, 2021 at 11:44:35 AM UTC-5, ed de moel wrote:
> To me, the most straightforward approach seems to be something like:
>
> Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
> . Set reference=$Query(@reference,-1) Quit:reference=""
> . Quit:reference'["""A"",""C"")"
> . Set count=count+1,result(reference)=1
> . Quit
> Quit
>
> (please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)
Ed wins! :-) I was putting together the same method, but he beat me to it.

OldMster schrieb am Freitag, 17. Dezember 2021 um 23:03:35 UTC+1:
> On Friday, December 17, 2021 at 11:44:35 AM UTC-5, ed de moel wrote:
> > To me, the most straightforward approach seems to be something like:
> >
> > Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
> > . Set reference=$Query(@reference,-1) Quit:reference=""
> > . Quit:reference'["""A"",""C"")"
> > . Set count=count+1,result(reference)=1
> > . Quit
> > Quit
> >
> > (please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)
> Ed wins! :-) I was putting together the same method, but he beat me to it.

I don't agree.
I'd code:
S count=0,time="" F S time=$O(^table(time),-1) Q:time=""!(count>9999) D
.I $D(^table(time,"A","C")) W time,^table(time,"A","C"),! S count=count+1
Q

Or do I overlook something?

On Friday, December 17, 2021 at 5:35:03 PM UTC-5, Jens wrote:
> OldMster schrieb am Freitag, 17. Dezember 2021 um 23:03:35 UTC+1:
> > On Friday, December 17, 2021 at 11:44:35 AM UTC-5, ed de moel wrote:
> > > To me, the most straightforward approach seems to be something like:
> > >
> > > Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
> > > . Set reference=$Query(@reference,-1) Quit:reference=""
> > > . Quit:reference'["""A"",""C"")"
> > > . Set count=count+1,result(reference)=1
> > > . Quit
> > > Quit
> > >
> > > (please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)
> > Ed wins! :-) I was putting together the same method, but he beat me to it.
> I don't agree.
> I'd code:
> S count=0,time="" F S time=$O(^table(time),-1) Q:time=""!(count>9999) D
> .I $D(^table(time,"A","C")) W time,^table(time,"A","C"),! S count=count+1
> Q
>
> Or do I overlook something?

Thank you for the responses. I will look into the query examples given.

Being so use to SQL syntax, I would have thought loops would be a inefficient way to do querying such as this. I guess as long as one writes well using loops the O(n) factor may be managed.

Gratitude,
KM

On Friday, December 17, 2021 at 5:35:03 PM UTC-5, Jens wrote:
> OldMster schrieb am Freitag, 17. Dezember 2021 um 23:03:35 UTC+1:
> > On Friday, December 17, 2021 at 11:44:35 AM UTC-5, ed de moel wrote:
> > > To me, the most straightforward approach seems to be something like:
> > >
> > > Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
> > > . Set reference=$Query(@reference,-1) Quit:reference=""
> > > . Quit:reference'["""A"",""C"")"
> > > . Set count=count+1,result(reference)=1
> > > . Quit
> > > Quit
> > >
> > > (please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)
> > Ed wins! :-) I was putting together the same method, but he beat me to it.
> I don't agree.
> I'd code:
> S count=0,time="" F S time=$O(^table(time),-1) Q:time=""!(count>9999) D
> .I $D(^table(time,"A","C")) W time,^table(time,"A","C"),! S count=count+1
> Q
>
> Or do I overlook something?

Thank you for the responses. I will look into the query examples given.

Being so use to SQL syntax, I would have thought loops would be a inefficient way to do querying such as this. I guess as long as one writes well using loops the O(n) factor may be managed.

Gratitude,
KM

In article <ec592130-7d33-4d67-843e-0b3b3cd15dc8n@googlegroups.com>,
ed de moel <eddemoel@gmail.com> wrote:
>To me, the most straightforward approach seems to be something like:
>
> Set max=10000,count=0,reference="^table" For Do Quit:reference="" Quit:count'<max
> . Set reference=$Query(@reference,-1) Quit:reference=""
> . Quit:reference'["""A"",""C"")"
> . Set count=count+1,result(reference)=1
> . Quit
> Quit
>
>(please note that this medium compresses double spaces to single ones, so there should be 2 spaces following the For, Do and Quit commands)

Shouldn't you also check that $QL(reference)=3

--
-- Rod --
rodd(at)polylogics(dot)com