|
|
So this query is driving me a little crazy
I have a table called quadrantids2017 which has a number of events submitted by different contributors, key fields are event_id which is unique and user_ID and datetime
what I want is a query that will bring back all events which ocoured on the same date within 30 seconds of each other but different contributors so here is some data
event ID, user_id, datetime
37775 2 2017-01-01 00:01:23
33500 1 2017-01-01 01:07:56
37776 2 2017-01-01 01:08:45
47827 3 2017-01-01 01:09:07
37777 2 2017-01-01 01:09:44
33501 1 2017-01-01 01:14:59
47828 3 2017-01-01 01:15:00
33502 1 2017-01-01 01:16:10
37778 2 2017-01-01 01:16:47
37779 2 2017-01-01 01:19:29
37780 2 2017-01-01 01:19:36
33503 1 2017-01-01 01:24:48
So i would want a result that looks like this
33500 1 2017-01-01 01:07:56
37776 2 2017-01-01 01:07:45
33501 1 2017-01-01 01:14:59
47828 3 2017-01-01 01:15:00
37780 2 2017-01-01 01:19:36
33503 1 2017-01-01 01:19:48
On issue I have is that my current attempts bring back matches from the same contributor
Help really appreciated
Regards
John B
|
|
|
|
|
Can you post your query and explain what records are returned?
|
|
|
|
|
ok my query is like this
SELECT
b.event_id AS a,
b.user_ID AS a,
b.date AS a,
b.Time AS a
FROM quadrantids2017 AS a
JOIN quadrantids2017 AS b
WHERE a.`date` = b.`date`
AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30
Note in this version I am using a separate date and time field
here are some of the results I have spaced and marked what should have been returned
37775 2 2017-01-01 00:01:23
33500 1 2017-01-01 01:07:56
37776 2 2017-01-01 01:08:45
37776 2 2017-01-01 01:08:45 - correct
47827 3 2017-01-01 01:09:07 - correct
47827 3 2017-01-01 01:09:07 - correct
37777 2 2017-01-01 01:09:44 - correct
33501 1 2017-01-01 01:14:59
33501 1 2017-01-01 01:14:59
47828 3 2017-01-01 01:15:00
47828 3 2017-01-01 01:15:00
33502 1 2017-01-01 01:16:10
John B
|
|
|
|
|
Looks like you're missing:
AND a.event_id != b.event_id from your WHERE clause.
(You might want to change that to an ON clause for the JOIN.)
If you want to exclude matches from the same user, you'll also need:
AND a.user_ID != b.user_ID
Or, to exclude matches for different users:
AND a.user_ID = b.user_ID
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
so this works fine
SELECT
b.event_id AS a,
b.user_ID AS a,
b.date AS a,
b.Time AS a
FROM quadrantids2017 AS a
JOIN quadrantids2017 AS b
WHERE a.`date` = b.`date`
AND a.event_id != b.event_id
AND a.user_ID != b.user_ID
AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30
my only query now is the length of time it took the record set its self has 3133 records and the query returned 2990 records but it took some 40 seconds to do ? - I understand its actually doing quite a lot
John B
|
|
|
|
|
spoke to soon, there are duplicates
37919 2 2017-01-01 11:43:02
37920 2 2017-01-01 11:43:23
47890 3 2017-01-01 11:42:56
47890 3 2017-01-01 11:42:56
47894 3 2017-01-01 12:13:31
33558 1 2017-01-01 12:13:29
47896 3 2017-01-01 13:24:44
37937 2 2017-01-01 13:24:17
37945 2 2017-01-01 14:09:53
37945 2 2017-01-01 14:09:53
sorry
|
|
|
|
|
I want to make sure I get the basic design correct from the beginning so help really appreciated.
Im creating a dbase (MySql) that will store Radio Meteor Events.
Individuals collate data on a monthly basis and a contributor may collect anywhere between 4,000 to 8,000 unique events per month, we currently have
6 contributors out of a possible 30 or so.
So planning ahead if all 30 contributed each month we could have around 250,00 event per month
The key purpose is to correlate event capture, so did anyone else capture the same event as me
Each event per contributor is unique and amongst other things each event records a full date yyyy/mm/dd and time HH:MM:SS
Once all this data is in a database there would be quite general queries like how many events on a particular date across all contributors say in the month of Jan how many events across all contributors match with regard to both date and time
Another common slightly more complex query would be to get all events for a particular date where the time matches with a tolerance of say 10 seconds either way
so if we had
Contributor 1 15:00:10
Contributor 1 15:00:30
Contributor 2 15:00:05
Contributor 2 15:01:10
Contributor 3 15:06:10
Contributor 3 15:00:10
It would return
Contributor 1 15:00:10
Contributor 2 15:00:05
Contributor 3 15:00:10
so i was planning two tables
1 - Contributors
2 - Events
Event ID would be unique and the primary key
the link between the two tables would be the Contributor ID
I’m not sure about date and time
I thought for flexibility I could have
Date
Time
Datetime
The data its self is quite simple but given this could grow I want to store it in the most appropriate way to ensure that queries are efficient
Help appreciated
Regards
John Berman
|
|
|
|
|
It is a very simple structure and the relationship between the table is correct.
There would be no benefit to storing the date and time in separate fields.
You may, in the future, want to look at creating aggregated summary tables to facilitate reporting but not for a year or more (assuming 250k per month).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I'm surprised, that the COLLATION used in a DB Create Statement does have influence in executing SQL statements. Following I testet:
A. Insensitive: Creating a DB explicitely "Case Insensitive"
CREATE DATABASE CIDB COLLATE Latin1_General_CI_AI;
GO
USE CIDB;
CREATE TABLE TEST (ID INTEGER);
SELECT * FROM TEST;
select * FROM TEST;
SELECT * FROM Test;
select * FROM Test;
:) --> Everything works like expected, MSSQL does not bother about "TEST" or "Test".
B. Sensitive: Creating a DB explicitely "Case Sensitive"
CREATE DATABASE CSDB COLLATE Latin1_General_CS_AI;
GO
USE CSDB;
CREATE TABLE TEST (ID INTEGER);
SELECT * FROM TEST;
Everyting up to here is working fine. But ...
SELECT * FROM Test;
??--> fails with error message "Msg 208, Level 16, State 1, Line 1 Invalid object name 'Test'."
... obviously because the database is created "Sensitive".
What's really surprising for me, that the collation sequence now also
makes the SQL Language a case sensitive, not the language it self but
the objects (table-, field- names and I assume also constraint names).
Am I the only one who is surprised by this?
Thank you in advance for some high lighting comments.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
It might seem odd at first glance, but it makes sense when you consider the fact that the names of all the objects within the database are stored in system tables within the database itself.
If the default collation for the database is case-sensitive, then the collation for all of the system tables is case-sensitive. So SELECT * FROM sys.tables WHERE name = 'Test' isn't going to match a row where name = 'TEST'.
Whilst Microsoft could have chosen to make the system tables always case-insensitive, that would have been a breaking change. Earlier versions of SQL Server didn't allow you to mix collations within a single database, so introducing that change in a later version could have broken existing databases or scripts.
There are effectively two "solutions":
- Always create your databases using a case-insensitive collation, and use a case-sensitive collation on individual columns;
- Always write your scripts using the case of the table and columns names as defined when they were created;
SQL Server Case Sensitive Collations and DMVs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Wow, great! Thank you for your speedy Response.
I think I will go with "1.". Little bit more work, but from my Point of view the most transparent way.
Thank you again.
Bruno
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Quote: that would have been a breaking change
After thinking again and again about it (I'm an old man and need therefor more time), is it not MS who is breaking the SQL Standard with this?
Not a big thing, only a thought.
Finally, it is like it is (with MSQL) and I have to live with it.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
0x01AA wrote: is it not MS who is breaking the SQL Standard with this?
Yes. It looks like SQL92 says that object names should be case-insensitive unless they are quoted.
I guess they decided the dangers of making the change outweighed the inconvenience of not following the standards.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks again to give me some confidence on this
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Hello experts
I’m usually organizing the data internally using a “system” generated id (at the moment int generated by mssql sequences) to define primary keys and foreign key relations. That has sometimes advantages but on the other hand it is not really the clean way from the point of theory (and for data exchange it can be the real pain).
Why?
The user usually needs also to have his “user-id” which is the user’s primary… so with the way above I introduce an extra index. And basically the user primary should enough and should be used to define the db constraints. But programmers are lazy and like keys all of the same type….
Ok, long speech. What I’m really like asking for Best Praxis is:
I’m at a point to migrate Interbase databases to MSSQL. Interbase has as default case sensitive indices while MSSQL has case insensitive as default.
In case this “user-id” allows alphanumeric, what is better/best Praxis/usual:
a.) Make Ids case sensitive ?
b.) Make Ids case insensitive?
Thank you in advance
Bruno
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Since you can do "like" and "sounds like" searches in SQL Server, you need to review your "requirements" more.
For the record, the recommended approach for "entity keys" are "nonsense numbers"; i.e. identities / generated id's. And they're central to Entity Framework's "tracking" ability.
It's the "user" ids / keys that get transported between systems; so they better match (case-wise); unless it's "universal", like an email; in which "case", you can case it the way you want when comparing.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Thank you, I need to think more about
Quote: 's the "user" ids / keys that get transported between systems; so they better match (case-wise); unless it's "universal", like an email.
Thanks again
Bruno
modified 19-Jan-21 21:04pm.
|
|
|
|
|
I find that the more systems I'm integrating with, the more I rely on creating "API's" where the object "getters" do some of the translating on the fly for a better or cleaner "view" within the context of the application. Molding reality.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Store all in upper-case and go for insensitive. Wouldn't make much sense most of the time to have a record with and one without capitalization in the database, would it?
For primary keys, I still recommend normalization. Your auto-generated identity is of little use to the user (and should not be visible).
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Quote: Wouldn't make much sense most of the time to have a record with and one without capitalization in the database, would it?
That is exactly the Point for which I'm looking for. Completely agree with you. Thank you for confirming this.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
You're welcome 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello,
I want to ask how Facebook database works like? For example if I consider only the FB posts that users put on this timeline, how that post table would work?
Some statistics.
1. There would be more than 1 million posts every second.
2. Other users may be commenting on that posts too.
Questions.
1. Is there only 1 post table or multiple posts table that contains user post based on their geographic location. This may help in reducing the load from a single post table.
2. Is Fb database has some different approach then the databases of normal (low traffic) website?
3. Every database whether Oracle or SQL server has a limit in terms of number of concurrent connection, no of inserts queries performing per second and so on. So how FB is doing this?
Hope my questions make sense. Anyone who might be in FB database team might help me in my questions?
thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
