Re: [sqlite] SQLite equivalent of SQL Over(Partition)

[Max Vlasov]

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  wrote:

> On 5 Apr 2018, at 11:41am, DThomas  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:
>
> 
>
> I think everything else used will continue to work.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite equivalent of SQL Over(Partition)

[Simon Slavin]

On 6 Apr 2018, at 8:58pm, R Smith  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:


On 5 Apr 2018, at 11:41am, DThomas  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:



I think everything else used will continue to work.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> On 5 Apr 2018, at 11:41am, DThomas  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:
>
> 
>
> I think everything else used will continue to work.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite equivalent of SQL Over(Partition)

[Simon Slavin]

On 5 Apr 2018, at 11:41am, DThomas  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:



I think everything else used will continue to work.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users