Multiple queries in phpmyadmin - Distance using coordinates, Slope, Intercept, Angle, and few more

I having around 500 excel sheets in .csv format with data captured for my experiment having following columns in place.

Now I need to calculate the following parameters using this data. I have done these in excel, however doing this repeatedly for each excel so many times is difficult, so I want to write an SQL query in PhpmyAdmin will help some time.

1. Last charecter typed - need to capture last charecter from the column 'CharSq'
2. *Slope (in column J) =(B3-B2)/(A3-A2)
3. Intercept (in column K) =B2-(A2*(J3))
4. Angle (in degrees) =MOD(DEGREES(ATAN2((A3-A2),(B3-B2))), 360) -
5. Index of Difficulty =LOG(((E1/7.1)+1),2)
6. Speed Value length (if speed value length >3, then mark as 1 or else 0) = =IF(LEN(D3) >= 3, "1","0")
7. Wrong Sequence (if I3=I2,then mark search time, else actual time) =IF(I3=I2,"Search Time","Actual Time")
8. Mark charecter into (1,2,3) = =IF(I2="A",1, IF(I2="B",2, IF(I2="C",3, 0)))

I have started with this SQL query SELECT id, type, charSq, substr(charSq,-1,1) AS TypedChar, xCoordinate, yCoordinate, angle, distance, timestamp, speed FROM table 1 WHERE 1

Need help for the rest of the parameters. Thanks.

Note - I am going to run this in phpMyAdmin SQL

Orginal Data captured (Indian language - so while uploading in excel / db make sure you have UTF8 format - Original data

Analysed data (desired SQL query) - All blue header columns and respective cell have formulaas in Sheet2

• Y'day I have updated it to MySQL- 8.0.18 version. Each excel will have around 500-600 such records. – Rupesh Nath Jun 22 '20 at 14:47
• Hi @Akina as per your suggestion, I have added the orginal data and analysed data as links. Let me know if you need more information. This is Indian Language so it should be set in UTF8. – Rupesh Nath Jul 3 '20 at 18:26

``````create table test.Table10 select mm.myid,mm.id,mm.type1 as GESTURE,MM.CHARSQ,MM.TYPE2 as TYPEDCHAR,MM.MYCHAR,MM.XCOR,MM.YCOR,MM.SLOPE,l4-(l2*(SLOPE)) as Intercept,
if (ANGLE1<0, (ANGLE1+360) , ANGLE1 ) as ANGLE0,MM.DISTANCE,MM.DW,MM.INDDIFF,MM.TIME1,MM.SPEED,MM.SPDFILT,MM.TIMETYPE from (select c11.*,((YCOR-l4)/(XCOR-l2)) as SLOPE,MOD(DEGREES (ATAN2((YCOR-l4),(XCOR-l2))), 360) as ANGLE1,(YCOR-l4)/(XCOR-l2) ATT,LOG2(((DW)+1)) as INDDIFF,
if(TYPE2=(LAG(TYPE2) OVER (
PARTITION BY MYID
ORDER BY ID)),"Search Time","Actual Time") as TIMETYPE,case when type2="A" then "1"
when type2="B" then 2
when type2="C" then 3
else 0
end as MYCHAR from (SELECT b.*,LEAD(XCOR) OVER (
PARTITION BY charsq) l1,LAG(XCOR) OVER (
PARTITION BY MYID
ORDER BY ID) l2,LEAD(YCOR) OVER (
PARTITION BY MYID) l3,LAG(YCOR) OVER (
PARTITION BY MYID
ORDER BY ID) l4,distance/7.1 as DW,IF(length(speed) >= 3, "1","0") as SPDFILT,RIGHT(charSq,1) as TYPE2 FROM test.table2 b) c11) mm
``````