Linux Scripting and SQL Scripting Example

For first subuser  then switch to super user,can be R user login to servers and give command to them.

#awk '$1 = "2023-07-05"' alert_orcl.log

server :/tmp # cat myfile.sh

UserName="xyz"

Password="xyzuserpassword"

Password_root="rootuserpassword"

RemoteCmd1="hostname; find /abc/xyz/data/ -name *.dat |grep -v punjabuniversity | awk -F '/' '{++S[\$5]} END {for(a in S) print a,S[a]}'"

for HOST in `cat /tmp/pu.txt |awk '{print $2}'`

do {

    expect<<EOF

    set timeout 3

    spawn ssh -o "StrictHostKeyChecking no" ${UserName}@${HOST}

    expect "*assword:"

    send "${Password}\n"

    expect "~>"

    send "su -\n"

    expect "*assword:"

    send "${Password_root}\n"

    expect "#"

    send "${RemoteCmd1}\n"

    expect "#"

    send "exit\n"

    expect "~>"

    send "exit\n"

    expect "#"

EOF

}

done

Host list for above script.

  pu.txt --file

 IP Adresss  name
-------------------------------------------------------------------------------------------------------------------------------------------

BASIC Linux Syntax and Loop & IF condition and basic scripts

user@host:~$ var_a="Hello World"
user@host:~$ another_var=42

{START..END..INCREMENT} syntax:
#!/bin/bash
echo "Bash version ${BASH_VERSION}..."
for i in {0..10..2}
  do
     echo "Welcome $i times"
 done
-----------------------------------------------------------------
#!/bin/bash
for i in {1..5}
do
   echo "Welcome $i times"
done
-------------------------------------------------------------------
for loop syntax

Numeric ranges for syntax is as follows:

for VARIABLE in 1 2 3 4 5 .. N
do
    command1
    command2
    commandN
done

OR

for VARIABLE in file1 file2 file3
do
    command1 on $VARIABLE
    command2
    commandN
done

OR

for OUTPUT in $(Linux-Or-Unix-Command-Here)
do
    command1 on $OUTPUT
    command2 on $OUTPUT
    commandN
done
-------------------------------------------------------------------------------------
for I in 1 2 3 4 5
do
  statements1      #Executed for all values of ''I'', up to a disaster-condition if any.
  statements2
  if (condition)
  then
    continue   #Go to next iteration of I in the loop and skip statements3
  fi
  statements3
done
------------------------------------------------------------------------------------------------
This script make backup of all file names specified on command line. If .bak file exists, it will skip the cp command.
#!/bin/bash
FILES="$@"
for f in $FILES
do
        # if .bak backup file exists, read next file
    if [ -f ${f}.bak ]
    then
        echo "Skiping $f file..."
        continue  # read next file and skip the cp command
    fi
        # we are here means no backup file exists, just use cp command to copy file
    /bin/cp $f $f.bak
done
---------------------------------------------------------------------------
#!/bin/bash

# for loop starts. it will iterate over all the files in current directory
for file in *\ *; do
    # it will replace spacesin file names
    mv "$file" "${file// /-}"
done
---------------------------------------------------------------------------
In the following example, it will check firefox.desktop file exists or not in applications/ directory.

#!/bin/bash

for file in /usr/share/applications/*
do
        if [ "${file}" == "/usr/share/applications/firefox.desktop" ]
        then
            echo "The file is found in applications directory";
        fi
done

------------------------------------------------------------------------------------

For Loop with Array Elements

By using For Loop we can iterate over array elements. It’s very easy to iterate over array elements. In the following example we are going to iterate over number ranges by using for loop:

#!/bin/bash

FRUITS=('Apple' 'Mango' 'Strawberry' 'Orange' 'Banana')

for item in "${FRUITS[@]}";
do
  echo "FRUIT: $item"
done
----------------------------------------------------
#!/bin/bash

# for loop for preinting 5 to 1 numbers
for (( i=5; i>=1; i-- ))
do 
   echo "COUNT: $i"
done
---------------------------
#!/bin/bash

for city in Tokyo London Paris Dubai Mumbai
do
  if [[ "$city" == 'Dubai' ]]; then
    break
  fi
  echo "CITY: $city"
done

echo "Loop Ended"
---------------------------------------------------
In the following example, it will check firefox.desktop file exists or not in applications/ directory.
#!/bin/bash
for file in /usr/share/applications/*
do
        if [ "${file}" == "/usr/share/applications/firefox.desktop" ]
        then
            echo "The file is found in applications directory";
        fi
done
In the following example, it will rename files names whose names contain spaces.

#!/bin/bash

# for loop starts. it will iterate over all the files in current directory
for file in *\ *; do
    # it will replace spacesin file names
    mv "$file" "${file// /-}"
done



---------------------------------------------------------------------------------------------------------------
  Example to write your  own SQL  STATEMENT in  Power  BI in Excel using currentWorkbook

let

    Source = Excel.CurrentWorkbook(){[Name="tablename"]}[Content],Query="SELECT * FROM [tablename]"

    

in

    Source

-------------------------------------------------------------------------------------------

VB SAMPLE CODE

C:\Users\ComputerName\AppData\Microsoft\Excel\XLSTART

Dim wbk As Workbook

Dim Filename As String

Dim Path As String

Application.DisplayAlerts = False

Path = "D:\PunjabUniversity\"  'CHANGE PATH

Filename = Dir(Path & "AG*.csv")

'--------------------------------------------

'OPEN EXCEL FILES

    Workbooks.Open ("D:\PunjabUniversity\AG.xlsx")

    Set wbk = Workbooks.Open(Path & Filename)

    '

    Range("A2:C2").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Copy

    Windows("AG.xlsx").Activate

    Sheets("Sheet1").Select

    Range("A2").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(1, 0).Select

    ActiveSheet.Paste

'OPEN EXCEL FILES

    Range("D2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, -1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

    'again for another kpi

    Windows("Combine.csv").Activate

    Sheets("AG_Combine").Select

    Range("D2:E2").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Copy

    Windows("AG.xlsx").Activate

    Sheets("Sheet1").Select

    Range("E2").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(1, 0).Select

    ActiveSheet.Paste

   

'Again Past the formula

    Range("G2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, -1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

        

'Again Past the formula

    Range("H2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, -1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

   

'Again Past the formula

    Range("I2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, -1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

    

 'J2 Column the formula

    Range("J2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, -1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

   

  'K2 Column the formula

    Range("K2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, -1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

   

     Range("K2").Select

     Selection.End(xlDown).Select

     WName = ActiveCell.Value

  

    ActiveWorkbook.RefreshAll

   

    Sheets("Sheet1").Select

  

   'PivotTable1

  

   With ActiveSheet.PivotTables("PivotTable3").PivotFields("Name")

   .PivotItems(WName).Visible = True

   End With

    

   Sheets("Partly").Select

  

   'PivotTable2

 

   With ActiveSheet.PivotTables("PivotTable3").PivotFields("Name")

   .PivotItems(WName).Visible = True

   End With

   

    ActiveWorkbook.Save

    ActiveWorkbook.Close

--------------------

Sub Macro_ABC()

Dim Sum_Of_drc_drops As Variant

Dim Sum_Of_drc_answers As Variant

Dim WName As Variant

Dim DName As Date

Dim wbk As Workbook

Dim Filename As String

Dim Path As String

Application.DisplayAlerts = False

Path = "D:\PunjabUniversity\"  'CHANGE PATH

Filename = Dir(Path & "ABC*.csv")

 '--------------------------------------------

'OPEN EXCEL FILES

    Workbooks.Open ("D:\PunjabUniversity\DEFV4.xlsx")

    Set wbk = Workbooks.Open(Path & Filename)

    '

   

    Range("A2:F2").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Copy

    Windows("DEFV4.xlsx").Activate

    Sheets("Raw Data").Select

    Range("A2").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(1, 0).Select

      

  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''code for the date extract

     

    dr1 = ActiveCell.Address

   

        

    ActiveSheet.Paste

   

    Selection.End(xlDown).Select

    

    dr2 = ActiveCell.Address

   

    

    '''get the date range  to

   Range(dr1 & ":" & dr2).Select

   Selection.Copy

   Range("G2").Select

   G2 = ActiveCell.Address

   ActiveSheet.Paste

   Selection.End(xlDown).Select

   gl = ActiveCell.Address

   Range(G2 & ":" & gl).RemoveDuplicates Columns:=1, Header:=xlNo

   Selection.End(xlUp).Select

   glafdup = ActiveCell.Address

   Range(G2 & ":" & glafdup).Select

   ctr = Range(G2 & ":" & glafdup).Count

   Dim NEWDateArray() As Variant

   NEWDateArray = Range(G2 & ":" & glafdup).Value

   For u = 1 To ctr

     MsgBox (NEWDateArray(u, 1))

  Next u

    

     

  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

  

     'For CASE1 Users

  

   

   ''''''''''''''''''''''

  

 Sheets("Raw data Pt2").Select

ActiveWorkbook.RefreshAll

With ActiveSheet.PivotTables("PivotTable10").PivotFields("date_t")

For u = 1 To ctr

 date_t = NEWDateArray(u, 1)

     .PivotItems(date_t).Visible = True

     Next u

  

End With

  

'''''' populate the data for the CASE1 USER Array,

Dim NEWArrayForHD_Users_KA() As Variant

  

Range("A6").Select

    Selection.End(xlDown).Select

       

    ActiveCell.Offset(-1, 1).Select

  

    HD_USEER_KA_LB_ADR = ActiveCell.Address

   

     ActiveCell.Offset(-(ctr - 1), 0).Select 'go upercell boundary

    

    HD_USEER_KA_UB_ADR = ActiveCell.Address

   

    Range(HD_USEER_KA_LB_ADR & ":" & HD_USEER_KA_UB_ADR).Select

   

    NEWArrayForHD_Users_KA = Range(HD_USEER_KA_LB_ADR & ":" & HD_USEER_KA_UB_ADR).Value

 

  

   For v = 1 To ctr

    MsgBox (NEWArrayForHD_Users_KA(v, 1))

   Next v

      

     

     

    

  

      Cells.Find(What:="KA", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _

        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

        , SearchFormat:=False).Activate

    Cells.FindNext(After:=ActiveCell).Activate

 

    ActiveCell.Offset(-1, 1).Select

   

    Dim NEWArrayForHD_Users_EA() As Variant

   

    

    'HD_User_Count_V1_EA = ActiveCell.Value

 

  

   

    HD_USEER_EA_LB_ADR = ActiveCell.Address

   

     ActiveCell.Offset(-(ctr - 1), 0).Select 'go upercell boundary

    

    HD_USEER_EA_UB_ADR = ActiveCell.Address

   

    Range(HD_USEER_EA_LB_ADR & ":" & HD_USEER_EA_UB_ADR).Select

   

    NEWArrayForHD_Users_EA = Range(HD_USEER_EA_LB_ADR & ":" & HD_USEER_EA_UB_ADR).Value

 

  

   For w = 1 To ctr

    MsgBox (NEWArrayForHD_Users_EA(w, 1))

   Next w

 

  

  

  

  

  

  

   'For CASE 2 Users

Sheets("Raw data Pt2").Select

ActiveWorkbook.RefreshAll

Windows("DEF.xlsx").Activate

Sheets("Raw data Pt2").Select

ActiveWorkbook.RefreshAll

   

With ActiveSheet.PivotTables("PivotTableSD").PivotFields("date_t")

For u2 = 1 To ctr

 date_t = NEWDateArray(u2, 1)

     .PivotItems(date_t).Visible = True

      Next u2

End With

  

   

Range("G6").Select

Selection.End(xlDown).Select

    ActiveCell.Offset(-1, 1).Select

    SD_User_Count_V1_KA = ActiveCell.Value

''''''''''''''''''''''''''''''''''''''''''''''

Dim NEWArrayForSD_Users_KA() As Variant

   

    

     

   

    SD_USEER_KA_LB_ADR = ActiveCell.Address

   

    ActiveCell.Offset(-(ctr - 1), 0).Select 'go upercell boundary

    

    SD_USEER_KA_UB_ADR = ActiveCell.Address

   

    Range(SD_USEER_KA_LB_ADR & ":" & SD_USEER_KA_UB_ADR).Select

   

    NEWArrayForSD_Users_KA = Range(SD_USEER_KA_LB_ADR & ":" & SD_USEER_KA_UB_ADR).Value

 

  

  

''''''''''''''''''''''''''''''''''''''''''''''''

''' populate for  data in the array

    

    

   

      Cells.Find(What:="KA", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _

        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

        , SearchFormat:=False).Activate

      Cells.FindNext(After:=ActiveCell).Activate

     

    ActiveCell.Offset(-1, 1).Select

    SD_User_Count_V1_EA = ActiveCell.Value

  

   

   

   Dim NEWArrayForSD_Users_EA() As Variant

   

    

     

   

    SD_USEER_EA_LB_ADR = ActiveCell.Address

   

    ActiveCell.Offset(-(ctr - 1), 0).Select 'go upercell boundary

    

    SD_USEER_EA_UB_ADR = ActiveCell.Address

   

    Range(SD_USEER_EA_LB_ADR & ":" & SD_USEER_EA_UB_ADR).Select

   

    NEWArrayForSD_Users_EA = Range(SD_USEER_EA_LB_ADR & ":" & SD_USEER_EA_UB_ADR).Value

 

   

   

   

   

   

   ''''''''''''''''''''''''''''''''''''''''

  

     

  

    

         

     Sheets("Working Sheet").Select

     Range("A1").Select

     Selection.End(xlDown).Select

     ActiveCell.Offset(1, 0).Select

     ActiveCell.Offset(0, 5).Select

     '''go to column F and past SD USER EA

         

    ActiveCell.Value = SD_User_Count_V7_EA

   

    

    '''NEWArrayForSD_Users_EA

   

     For sdea = 1 To ctr

    MsgBox (NEWArrayForSD_Users_EA(sdea, 1))

   

    ActiveCell.Value = NEWArrayForSD_Users_EA(sdea, 1)

    ActiveCell.Offset(1, 0).Select

   Next sdea

   

    

 '''go to column G and past CASE1 USER EA

  Range("G2").Select

 Selection.End(xlDown).Select

 ActiveCell.Offset(1, 0).Select

For hdea = 1 To ctr

ActiveCell.Value = NEWArrayForHD_Users_EA(hdea, 1)

ActiveCell.Offset(1, 0).Select

Next hdea

    

Range("E2").Select

Selection.End(xlDown).Select

Selection.Copy

ActiveCell.Offset(0, 1).Select

Selection.End(xlDown).Select

ActiveCell.Offset(0, -1).Select

Range(Selection, Selection.End(xlUp)).Select

ActiveSheet.Paste

    

    Range("F2").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(1, 0).Select

     '''

       '''go to column F and past SD USER KA

         

          

          

          

   

   For sdka = 1 To ctr

     ActiveCell.Value = NEWArrayForSD_Users_KA(sdka, 1)

   ActiveCell.Offset(1, 0).Select

   Next sdka

         

    'ActiveCell.Value = SD_User_Count_V7_KA

 

    ''''''''

  

     '''

       '''go to column G and past HD USER KA

      Range("G2").Select

    Selection.End(xlDown).Select

     ActiveCell.Offset(1, 0).Select

    

     'NEWArrayForHD_Users_KA(v, 1)

    

     

      For hdka = 1 To ctr

     ActiveCell.Value = NEWArrayForHD_Users_KA(hdka, 1)

   ActiveCell.Offset(1, 0).Select

   Next hdka

    

     

   

    

    Range("E2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, 1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, -1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

   

    

    Range("H2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, -1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

   

    

    

    Range("I2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, -1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

   

    

  

    

    'COPY KA FROM LAST CELL OF D2 AND PAST IN THE LAST 7 CELL OF THE SMAME COLUMN

    Range("D2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, 1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, -1).Select

    Range(Selection, Selection.Offset(-(ctr - 1), 0)).Select

    'Selection.Offset(-1, 0).Select

    ActiveSheet.Paste

   

    ''''''' AGAIN COPY EA FROM D2 AND PAST IN THE LAST 7 CELL OF THE SMAME COLUMN

    Range("D2").Select

    Selection.Copy

    Selection.End(xlDown).Select

    ActiveCell.Offset(1, 0).Select

    Range(Selection, Selection.Offset(ctr - 1, 0)).Select

    'Selection.Offset(-1, 0).Select

    ActiveSheet.Paste

   

    '''''''''''''''''''''''''''''''''''

   Selection.End(xlUp).Select

    Range("C2").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(1, 0).Select

    

     

     For dt = 1 To ctr

     MsgBox (NEWDateArray(dt, 1))

      ActiveCell.Value = NEWDateArray(dt, 1)

   ActiveCell.Offset(1, 0).Select

  Next dt

    

  ActiveCell.Offset(-1, 0).Select

    Range(Selection, Selection.Offset(-(ctr - 1), 0)).Select

   Selection.Copy

   Selection.End(xlDown).Select

   ActiveCell.Offset(1, 0).Select

   Range(Selection, Selection.Offset((ctr - 1), 0)).Select

   ActiveSheet.Paste

  

  '''copy formula B2

    Range("B2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, 1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, -1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

    

    

    '''copy formula B2

    Range("A2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, 1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, -1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

    

     

     

    Range("J2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, -1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

   

    

    

    Range("K2").Select

    Selection.End(xlDown).Select

    Selection.Copy

    ActiveCell.Offset(0, -1).Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(0, 1).Select

    Range(Selection, Selection.End(xlUp)).Select

    ActiveSheet.Paste

   

    

    Range("K1").Select

    Selection.End(xlDown).Select

    'store the WeekName from last cell of I column

    WName = ActiveCell.Value

   

     

    Sheets("Partly").Select

   ActiveWorkbook.RefreshAll

    With ActiveSheet.PivotTables("PivotTable14").PivotFields("Name")

   .PivotItems(WName).Visible = True

    End With

   

    

    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Name")

   .PivotItems(WName).Visible = True

    End With

   

        

    ActiveWorkbook.Save

    ActiveWorkbook.Close

   

End Sub

 ------------------------------------------------------------------------------------------------------------

USE OF INSTR()  & SUBSTR() Function in ORacle

-----------------------------------------------------------------

SELECT substr('ABC_DEFGHIJ-KLMNOP_XYZ.csv',INSTR('ABC_DEFGHIJ-KLMNOP_XYZ.csv','_',-1,2)+1,(INSTR('ABC_DEFGHIJ-KLMNOP_XYZ.csv','_',-1,1)-INSTR('ABC_DEFGHIJ-KLMNOP_XYZ.csv','_',-1,2))-1) from dual

SELECT INSTR('SORSORAZE BDOOR','OR', 3, 2) "Result"  FROM DUAL;

 

  Result

----------

        14

SELECT INSTR('SORSORAZE BDOOR','OR', -3, 2) "Result in Reversed"  FROM DUAL;

 

Result in Reversed

-----------------

                2

SQL Scripting Example using partition by and order by clause with rank function

--------------------------------------------------------------------------------------

with temp as (
              select
              id,
              Amount_KG as WeightKG,
              dense_rank() over (order by Amount_KG  desc) as ranks,
              app_id
                 from
                  (
                  select
                  id,
                  SUM(D1+D2)/9.8 Weight_KG,
                  your_id
                  from yourTableName
                  where
                  id like 'abc%'
          and date='20220101'
                  group by  id,your_id
                  )A              ---note all the columns are alreay groupy in the inner query no need to group by the out query in the temp table
                        
          )--end temp
     
  --query temp
  select ranks,id, WeightKG,your_id from temp where ranks>=1    and ranks<=10
------------------------------------------------------------------------------------------------------------------------------------------------------
getting the your top your_ids for each of ids having order by weighKG wise
-----------------------------------------------------------------------------------------------------------------------------------------------------
with temp as (
                         select
                         your_id,
                         id,
                         Weight_KG,
                 dense_rank() over (partition by id order by Weight_KG desc) as ranks
                from
                (
                select
                your_id,
                id,
                SUM(d1+d2) Weight_KG
                from yourTableName
                where
                id in('abc123',  'edf456') --your given ids
                and DATE='20201202'
                group by  your_id,id
                )A
                          
                        )
               select ranks,your_id,id, Weigh_KG from temp where ranks>=1  and ranks<=10

-----------------------------------------------------------------------------------------------------------------------------Cal PH using Row_Number Function 

----------------------------------------------------------------------------------------------------------------------------------------------------------

with temp as (

select substr(`name`,1,10) as ABC_NAME,

Hr,

sum(`Pra`)  Pk_Ta,

sum(`wC`)    Cp,

sum(`Pra`)/sum(`WC`) Ut

from df

 --where Name='ABCDEFG'

 group by ABC_NAME,Hr

 ),

 t2 as (select ABC_NAME,

        Hr, 

        Pk_Ta,

        Ut,

        max(Pk_Ta) over(PARTITION BY Hr) Mx_Ta 

        from temp 

        order by Pk_Ta desc

        ), 

        

        t3 as(

 select row_number() over(partition by ABC_NAME) N,* from t2

        )

select * from t3 where N=1