Recent

Author Topic: Sorting and Counting  (Read 14767 times)

mpknap

  • Full Member
  • ***
  • Posts: 135
Re: Sorting and Counting
« Reply #210 on: July 12, 2020, 12:04:50 pm »
 
Quote

Code: SQL  [Select][+][-]
  1. SELECT  datetime(TIMESTAMP/1000,'unixepoch') AS czas,  
  2. COUNT(datetime(TIMESTAMP/1000,'unixepoch')) AS dupes,
  3. GROUP_CONCAT( DISTINCT device_id  ) AS dup_ids
  4. FROM detections
  5. GROUP BY datetime(TIMESTAMP/1000,'unixepoch')
  6. HAVING dupes > 2 AND COUNT(DISTINCT device_id) > 2

I'm not sure you even need the dupes column then??
Code: SQL  [Select][+][-]
  1. SELECT  datetime(TIMESTAMP/1000,'unixepoch') AS czas,  
  2. GROUP_CONCAT( DISTINCT device_id  ) AS dup_ids
  3. FROM detections
  4. GROUP BY datetime(TIMESTAMP/1000,'unixepoch')
  5. HAVING COUNT(DISTINCT device_id) > 2
??


Yes! Both codes give the same and correct results. Now the results are clear and transparent .Thanks :) 

Quote
Other than that I am also unable to test it further as the provided dataset does not contain any data matching the criteria.

Quote
@mpknap: as stated before: Change the objective and you can start redesigning your statement(s)  ;)

Yes, I know, but unfortunately the original SQLITE file is 4.5GB.


Quote
Have you considered creating your own custom function(s) using Pascal ? see also: http://www.sqlite.org/c3ref/create_function.html as unfortunately SQLite does not seem to support the statement "create function".

Interesting, but possible in Pascal?

Thaddy

  • Hero Member
  • *****
  • Posts: 10294
Re: Sorting and Counting
« Reply #211 on: July 12, 2020, 12:31:14 pm »
Interesting, but possible in Pascal?
Of course. I use that all the time for my special needs... Mind the cdecl for your external libraries, though.
I am more like donkey than shrek

rvk

  • Hero Member
  • *****
  • Posts: 4227
Re: Sorting and Counting
« Reply #212 on: July 12, 2020, 12:37:53 pm »
Quote
@mpknap: as stated before: Change the objective and you can start redesigning your statement(s)  ;)
Yes, I know, but unfortunately the original SQLITE file is 4.5GB.
Yikes. That's more than the 104.000 lines you gave before. That illustrates the point extra. You should have mentioned that at the beginning. A simple one TStringList solution with sorting in memory isn't really feasible in that case and we would have suggested a DB solution from the beginning.

TRon

  • Sr. Member
  • ****
  • Posts: 432
Re: Sorting and Counting
« Reply #213 on: July 12, 2020, 07:07:11 pm »
Interesting, but possible in Pascal?
As Thaddy already wrote, yes

For an example see FreePascal package fcl-db/examples/sqlite3extdemo.pp (and accompanied myext.pp)

Yes, I know, but unfortunately the original SQLITE file is 4.5GB.
Ah, the final requirements/conditions. It took only #210 posts  ;)

As rvk already wrote, a vital piece if information that should have been mentioned from the start imho. Even in case you are not able to share (all) the data. It just makes it a bit more difficult to verify (at least in my case, since i'm fairly new to SQLite).

 

TinyPortal © 2005-2018