Write a view in MySQL

Ditutup

Deskripsi

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.

Keahlian MySQL

Lihat lebih lanjut: software write mq4, software write chip epson, useful software write book, software write web specs, php mysql view edit sample, move mysql view, free software write user guide, php mysql view add edit script, mysql slow write, software write edid, free software write company profile, software write websites idea, javascript mysql view edit add, software write book images, php mysql view debug, software write books, write sql web front end, software write protection, free software write book, upload photo mysql view, software write book, mysql view creator, mysql view, speed mysql view, market research open end coding job

ID Proyek: #11721850

12 freelancer menawar pada rata-rata ₹1429 untuk pekerjaan ini

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- Lagi

₹1350 INR dalam 1 hari
(3 Ulasan)
3.9
phiona06

Hi! I create SQL: SET @mod := 3; SELECT `EquityId`, `Open2` AS `Open`, min(`Low`) AS `Low`, max(`High`) AS `High`, `Close2` AS `Close`, sum(`Volume`) AS `Volume`, `SnapshotDate` , `SnapshotDateTime`, `SnapshotTime` , Lagi

₹1750 INR dalam 0 hari
(7 Ulasan)
3.5
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 dalam 3 hari
(16 Ulasan)
3.4
shaochao555

How are you. I am a web developer. I did review your requirements. I have experience skills related to your job. Let's go to good results with me. Please ping me.

₹1750 INR dalam 2 hari
(7 Ulasan)
3.4
arkdotnetec12

Dear Sir, We specialize in web development and have developed over 100+ web portals We are dynamic and energetic group of professionals. We value our customers and are committed to delivering high quality results th Lagi

₹1600 INR dalam 1 hari
(0 Ulasan)
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 Lagi

₹1000 INR dalam 1 hari
(0 Ulasan)
0.0
fabianrussell

A proposal has not yet been provided

₹1250 INR dalam 2 hari
(0 Ulasan)
0.0
shanymathew

Dear Sir, please hire me.i wil help you.am very good in sql.i have 5+ year experience in php,mysql,sql etc i can do it sir. i hope i wil get hired by you.message me

₹1300 INR dalam 1 hari
(0 Ulasan)
0.0
₹1500 INR dalam 1 hari
(0 Ulasan)
0.0
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 Lagi

₹1300 INR dalam 0 hari
(0 Ulasan)
0.0
₹1300 INR dalam 1 hari
(0 Ulasan)
0.0
₹1300 INR dalam 1 hari
(0 Ulasan)
0.0