Write a view in MySQL

Encerrado Postado há 7 anos Pago na entrega
Encerrado Pago na entrega

I have a table whose DDL is:

CREATE TABLE `Historical_Intraday_Prices` (

`EquityId` int(11) NOT NULL,

`SnapshotDateTime` timestamp NOT NULL,

`Open` double NOT NULL,

`Low` double NOT NULL,

`High` double NOT NULL,

`Close` double NOT NULL,

`Volume` double NOT NULL,

`SnapshotDate` date NOT NULL,

`SnapshotTime` time NOT NULL,

`UpdateToDBTime` timestamp NOT NULL,

`DataSourceName` varchar(50) NOT NULL,

PRIMARY KEY (`EquityId`,`SnapshotDate`,`SnapshotTime`),

KEY `IDX_SNAPSHOTDATE` (`SnapshotDate`),

KEY `IDX_SNAPSHOTDATETIME` (`SnapshotDateTime`),

CONSTRAINT `FKEquityId` FOREIGN KEY (`EquityId`) REFERENCES `EquityMaster` (`EquityId`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1

The snapshotdatetime is a one minute interval. So for a particular EquityId and a particular SnapshotDate, we have per minute data for that day and equity. If the entire market duration is 375 minutes for a particular day, then this table contains 375 records. This means the table stores 1 minute timeframe data for an equity.

The attached file will contain n example. This output is a result of running the below query

SELECT H.* FROM Historical_Intraday_Prices H, EquityMaster E

WHERE E.`EquityId`=H.`EquityId`

AND E.`CompanySymbol`="SBIN"

AND H.`SnapshotDate`='2016-09-01'

I need to create a view so that the output will be a 3 minute timeframe data

Something like running this query:

SELECT H.* FROM Historical_Intraday_Prices_3Timeframe H, EquityMaster E

WHERE E.`EquityId`=H.`EquityId`

AND E.`CompanySymbol`="SBIN"

AND H.`SnapshotDate`='2016-09-01'

This will aggregate the Historical_Intraday_Prices by combining every 3 minute data and then reporting out the same set of columns.

So if the 1 minute timeframe table has 375 rows for a particular equity and particular equity, the output from the view should return 375/3 =125 rows

Calculation:

SnapshotTime should be 9:15, 9:18, 9:21, etc

The open should be the open price at the start of the 3 minute interval

The close should be the close price at the end of the 3 minute interval

The high price should be the high price for the 3 minute duration

The low price should be the low price for the 3 minute duration

Th volume should be the volume sum of all the 3 minutes

The variable inside the view creation script having this timeframe minute which in our case is 3, should be stored as a main variable at the top of the script so that I can change it to 5 minutes if required and then entire process will work for 5 minute timeframe.

MySQL

ID do Projeto: #11721850

Sobre o projeto

11 propostas Projeto remoto Ativo em há 7 anos

11 freelancers estão ofertando em média ₹1400 nesse trabalho

GeniuSBraiN

Hello, I can do the task in less time, and 100% accurate - I'm an experienced software developer specialized in desktop applications, tools, and scripts. - Very good Experience with database based applications (MS- Mais

₹1350 INR em 1 dia
(3 Comentários)
3.9
davedcb

Hello, I am interested in your project. I am a software engineer and I have professional experience with SQL. Thank you, Best regards, David

₹1750 INR in 3 dias
(18 Comentários)
3.5
RicsonWebMaster

Hello, After i read your job description, i'm very suitable and interested for it, I'm Web developer / Designer for more than 5 years, i'm just new here in freelancer but i can assure you the best speed and quality Mais

₹1300 INR in 0 dias
(0 Comentários)
0.0
JasperExpert

I have understood your requirement completely, Basically you need an aggregate table/view on top of your existing table. This can be easily achieved using any ETL tools. I'm an Oracle professional having 5 years of exp Mais

₹1000 INR em 1 dia
(0 Comentários)
0.0
fabianrussell

A proposal has not yet been provided

₹1250 INR in 2 dias
(0 Comentários)
0.0