A Different Problem with Full Details
-
Hey guys,
I have a SQL problem.
1-) Firstly mine sql design ;
2-) Default SQL query
3-) But in some query results the values are partly the same and partly different.
4-) For this reason I grouped the coins and tarih columns. There are 100 different coins in the system. For this reason, I can capture the last minute data like this.
5-) I can get the data from 3 minutes ago.
6-) Here is my problem 🙂
7-) And here 🙂
Codes :DECLARE @LastChangeDate AS smalldatetime;
SET @LastChangeDate = getDate();Select a.tarih,a.coin,(A.price),b.tarih,b.coin,(B.price),(A.price/B.price) as 'Divide' from (SELECT tarih, coin, MIN(price ) AS price
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin OFFSET 300 ROWS
FETCH NEXT 100 ROWS ONLY) A,
(SELECT Top 100 tarih, coin, MIN(price ) AS price
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin) BDECLARE @LastChangeDate AS smalldatetime;
SET @LastChangeDate = getDate();Select a.tarih,a.coin,(A.price),b.tarih,b.coin,(B.price) from (SELECT tarih, coin, MIN(price ) AS price
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin OFFSET 300 ROWS
FETCH NEXT 100 ROWS ONLY) A,
(SELECT Top 100 tarih, coin, MIN(price ) AS price
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin) BDECLARE @LastChangeDate AS smalldatetime;
SET @LastChangeDate = getDate();SELECT Top 100 tarih, coin, MIN(price) AS price
FROM dbo.cointahmin where @LastChangeDate>tarih
GROUP BY tarih, coin order by tarih desc,coinSELECT tarih, coin, price
FROM dbo.cointahminIs it understandable enough? Thanks 🙂
-
First, sort out your database design. If a column is going to hold money data, give it a decimal data type, not nvarchar. If the data in your column is always seven characters wide, define it as (n)char(7). And if you've got duplicates in your data (or even if you haven't), add a primary key or unique constraint on tarih and index.
Now the query. You're doing a cross join between the results of the two subqueries. If each subquery returns a thousand rows, the cross join will return a million. Read about and understand the new-style join syntax (INNER JOIN, LEFT JOIN, RIGHT JOIN etc) and use it. I imagine you want to join on coin = coin and datediff = 3 minutes, don't you? Read about windowing functions as well: you may be able to use LAG or LEAD to avoid joins altogether.
That's the best advice I can give in the absence of table DDL and sample data.
John
-
This is the same problem as here. Don't start a new thread just because you don't like the answers that you are getting on your original thread.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Thursday, January 25, 2018 9:24 AM
🙂 Bro I'm already on that subject. They just wanted detailed information.
- xtrmus2319 - Thursday, January 25, 2018 9:27 AM
You will find the same on this thread. Best to revert back to the original thread and continue there.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - xtrmus2319 - Thursday, January 25, 2018 9:27 AM
Well, if you want an answer, you need to provide the details.
For starters, your design is flawed. it appears, based upon the limited information you have provided, that this can be normalized into 2 separate tables. One for coin, the other for values and date.
Secondly, again, you are getting exactly what you are asking for in this query.If you want to provide code to create the tables (DDL), some consumable data to fill those tables, and a sample of what you expect the output to be, then you will probably get some real answers.
If you do not know how to provide this information, then please read the links that are in multiple replies to your posts.Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - xtrmus2319 - Thursday, January 25, 2018 9:27 AM
The detailed information should have been provided in the original thread instead of starting a new thread. Starting a new thread fragments the discussion.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Before getting into any of the deeper issues with this, you still haven't addressed the cartesian join which was brought up multi times in the last thread you started is a basic function of SQL queries and explains what you are seeing in #6.
- drew.allen - Thursday, January 25, 2018 9:33 AM
Bit of déjà vu here it seems. As Drew said stick to one thread.
...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply