SQLite equivalent of SQL Over(Partition)

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite equivalent of SQL Over(Partition)

DThomas
Hello I have the following query in SQL Server 2008. The database has been
moved to a mobile device using SQLite. Can anyone help with a equivalent
statement for SQLite?

Select DISTINCT Sites.Customer, Sites.Digit,
Count(TblContractTasks.TaskNumber)
OVER (PARTITION BY Sites.Digit)  As TaskCount
FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On
Sites.Digit = TblContractDetails.SiteDigit) On
TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite equivalent of SQL Over(Partition)

Simon Slavin-3
On 5 Apr 2018, at 11:41am, DThomas <[hidden email]> wrote:

> Select DISTINCT Sites.Customer, Sites.Digit,
> Count(TblContractTasks.TaskNumber)
> OVER (PARTITION BY Sites.Digit)  As TaskCount
> FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On
> Sites.Digit = TblContractDetails.SiteDigit) On
> TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
> WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit

Instead of PARTITION BY use GROUP BY .  See "with COUNT function" example here:

<http://www.sqlitetutorial.net/sqlite-group-by/>

I think everything else used will continue to work.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite equivalent of SQL Over(Partition)

Don V Nielsen
That seems like an odd application of OVER (Partition by). Is there some
performance reason one would want to do DISTINCT OVER (PARTITION BY)
instead of a simple GROUP BY Sites.Customer, Sites.Digit, Count()?


On Fri, Apr 6, 2018 at 12:20 PM, Simon Slavin <[hidden email]> wrote:

> On 5 Apr 2018, at 11:41am, DThomas <[hidden email]> wrote:
>
> > Select DISTINCT Sites.Customer, Sites.Digit,
> > Count(TblContractTasks.TaskNumber)
> > OVER (PARTITION BY Sites.Digit)  As TaskCount
> > FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On
> > Sites.Digit = TblContractDetails.SiteDigit) On
> > TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
> > WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit
>
> Instead of PARTITION BY use GROUP BY .  See "with COUNT function" example
> here:
>
> <http://www.sqlitetutorial.net/sqlite-group-by/>
>
> I think everything else used will continue to work.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite equivalent of SQL Over(Partition)

R Smith

On 2018/04/06 9:15 PM, Don V Nielsen wrote:
> That seems like an odd application of OVER (Partition by). Is there some
> performance reason one would want to do DISTINCT OVER (PARTITION BY)
> instead of a simple GROUP BY Sites.Customer, Sites.Digit, Count()?

Agreed, in fact half that query seems redundant, it would work, but my
guess is Mr. Thomas inherited it from someone else who did not fully
know what they wanted to achieve, then googled a solution and found a
hit on an old stackoverflow question that was sort-of like what they
wanted, but not exactly.

The reason I say that is that the OVER (PARTITION BY) functionality in
MSSQL /can/ produce some amazing results that are very hard to do in
SQLite (not impossible though, just difficult), but the shown query is
not such a case, it's a straight-forward grouping, unless the query was
simplified for posting, or the expected results were different than what
we assume.

As to the solution, It's a bit like asking how you say "Cowabunga dude!"
in Swahili - there is no amount of translation that would reproduce the
sentiment. I think the tutorial from Simon's post will shed light, but
Dean, if you still have trouble, could you perhaps show us your schema
and explain what you want to know, because now we simply assume that
shown query actually gives exactly what you wanted, which may or may not
be the case. Tell us what the query output should reveal or look like,
and someone here (or several someones) should come up with real good
queries for the job.


>
>
> On Fri, Apr 6, 2018 at 12:20 PM, Simon Slavin <[hidden email]> wrote:
>
>> On 5 Apr 2018, at 11:41am, DThomas <[hidden email]> wrote:
>>
>>> Select DISTINCT Sites.Customer, Sites.Digit,
>>> Count(TblContractTasks.TaskNumber)
>>> OVER (PARTITION BY Sites.Digit)  As TaskCount
>>> FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On
>>> Sites.Digit = TblContractDetails.SiteDigit) On
>>> TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
>>> WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit
>> Instead of PARTITION BY use GROUP BY .  See "with COUNT function" example
>> here:
>>
>> <http://www.sqlitetutorial.net/sqlite-group-by/>
>>
>> I think everything else used will continue to work.
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite equivalent of SQL Over(Partition)

Simon Slavin-3
On 6 Apr 2018, at 8:58pm, R Smith <[hidden email]> wrote:

> my guess is Mr. Thomas inherited it from someone else who did not fully know what they wanted to achieve, then googled a solution and found a hit on an old stackoverflow question that was sort-of like what they wanted, but not exactly.

[snip]

> there is no amount of translation that would reproduce the sentiment. I think the tutorial from Simon's post will shed light, but Dean, if you still have trouble, could you perhaps show us your schema and explain what you want to know

That was my problem.  I don't use SQL Server and I don't know what would make someone who did reach for PARTITION BY, and I don't like cursor functions in SQL.  It seemed like they wanted GROUP BY but I wasn't sure.  Therefore I was loth to post an exact query.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite equivalent of SQL Over(Partition)

Max Vlasov
In reply to this post by Simon Slavin-3
Simon,
I think the Group by might work correctly, but sometimes (as in OP case)
would require a lot of rewriting (copy-paste). The key point here is that
the Window function doesn't change the set, but only allows wider access to
other rows of the set at the current row "time". So we just have to move
the group by to the column.

An example.

The good answer to a PARTITION BY question is at

https://stackoverflow.com/questions/2404565/sql-server-difference-between-partition-by-and-group-by#30907639
(I enforced the second popular answer)

The table
  is CREATE TABLE [TableA] ([id] integer, [firstname] text, [lastname]
text, [Mark] integer)

and the PARTITION BY query from the answer

  select SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname from TableA

can be rewritten in SQlite as

  select (select sum(mark) from TableA grpTableA where
grpTableA.id=TableA.id) as marksum, firstname from TableA

The problem with the rewriting of OP query is that we have to move the
whole FROM TblContractTasks ... part to the column with the group by
sub-query. Not only the query becomes less readable, we will probably get
some performance penalty unless Sqlite does some optimization for the
column sub-query. But at least it's formally possible.

Max


On Fri, Apr 6, 2018 at 8:20 PM, Simon Slavin <[hidden email]> wrote:

> On 5 Apr 2018, at 11:41am, DThomas <[hidden email]> wrote:
>
> > Select DISTINCT Sites.Customer, Sites.Digit,
> > Count(TblContractTasks.TaskNumber)
> > OVER (PARTITION BY Sites.Digit)  As TaskCount
> > FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On
> > Sites.Digit = TblContractDetails.SiteDigit) On
> > TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
> > WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit
>
> Instead of PARTITION BY use GROUP BY .  See "with COUNT function" example
> here:
>
> <http://www.sqlitetutorial.net/sqlite-group-by/>
>
> I think everything else used will continue to work.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users