/*过滤，表emp存储各部门员工信息*/

select eid, name,   deptid, salary from emp where salary between 8000 and 10000 and   hireday>='2010-01-01'

/*排序*/

select * from emp   order by salary

 /*去重*/

select distinct   deptid from emp

/*分组汇总，表share存储某支股票每天的收盘价*/

select   year(sDate),month(sDate),max(price) from share group by   year(sDate),month(sDate)

/*关联，表dept存储部门信息*/

select e.name,   e.salary, d.deptname from emp e inner join dept d on e.deptid=d.deptid

/*开窗，按工资对各部门内的员工进行排名*/

select eid, name,   deptid, salary, rank()over( partition by deptid order by salary desc) rk   from emp

with tmp as (

        select dept, count(*) total from   employee group by dept),

deptmax as(

        select dept from tmp where total=(

                select max(total)  from tmp)

        ),

deptmin as(

       select dept from tmp where total=(

               select min(total) from tmp)

        )

select dept  from deptmax

union

select dept from   deptmin

select   max(consecutive_days)

from   (select count(*) consecutive_days

      from (select sum(updown_flag)   over(order by sdate) no_up_days

            from (select sDate,

                      case when

                          price>LAG(price)   over(order by sDate)

                     then 0 else 1 end updown_flag

                  from share) )

group by   no_up_days)

with std as(

      select 1   No,'Sun.' name from dual union

      select 2   ,'Mon.'  from dual union

      select 3   ,'Tues.'  from dual union

      select 4   ,'Wed.'  from dual union

      select 5   ,'Thur'  from dual union

      select 6   ,'Fri.'  from dual union

      select 7   ,'Sat.'  from dual

      )

select std.No,std.name,data.total from std left join   (     

    select   DAYOFWEEK(orders.orderdate) No,sum(amount) total

    from orders

    where amount>15000

    group by   DAYOFWEEK(orders.birthday)

) data

on   std.No=data.No order by std.No

/*切换库、建表、入库，最后建主键索引，可大幅提升导入速度*/

mysql>use testdb;

mysql>create   table emp (

->   empid int(10) not null,

->   name varchar(50),

    -> deptid int(10),

    -> salary float,

    -> sex varchar(1),

    -> birthday date,

->   hireday date)CHARSET = utf8;

mysql>LOAD DATA   INFILE 'd:\data\emp.csv' INTO TABLE emp

   ->CHARACTER SET utf8

   ->FIELDS TERMINATED BY ','

   ->LINES TERMINATED BY '\r\n'

   ->IGNORE 1 LINES;

mysql>ALTER TABLE   emp ADD PRIMARY KEY (empid);

Const Title As String = "IMPORT CSV TEST"

Sub fMain()
      Dim fTextDir As String
      Dim pintLen As Integer
      Dim pstrValue As String
      Dim rowIndex As Integer
      Dim i As Integer
      rowIndex = 1
      pstrValue = ""
      pintLen = Len(Title)
      fTextDir = "D:/orders.csv"
      Open fTextDir For Input As #1
      Do While Not EOF(1) ' loop every line
          Line Input #1, currLine
          If Right(currLine, pintLen) =   Title Then
          Range(Cells(rowIndex, 1),   Cells(rowIndex, 4)).Select
           With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlTop
              .WrapText   = False
                .Orientation = 0
              .AddIndent   = False
                .ShrinkToFit = False
                .ReadingOrder = xlContext
              .MergeCells   = True
              .RowHeight   = 27.75
              .Font.Name   = "Arial"
              .Font.Size   = 18
              .Font.Bold   = True
                .FormulaR1C1 = Title
                .Interior.ColorIndex = 6
                .Interior.Pattern = xlSolid
          End With
          Else
          rowDataArr = Split(currLine,   ",")
          For i = 0 To UBound(rowDataArr)
                Cells(rowIndex, i + 1).Select
              With   Selection
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlTop
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = True
                    .RowHeight = 20
                    .Font.Name = "Arial"
                    .Font.Size = 12
                    .Font.Bold = False
                    .FormulaR1C1 = rowDataArr(i)
              End With
          Next i
          End If
          rowIndex = rowIndex + 1
      Loop
      Close #1
  End Sub

Public Sub test()
  Dim Arr
  Dim MyRng As Range
  Dim i As Long
  Dim Dic As Object
 
  Set MyRng = Range("A1").CurrentRegion
  Set MyRng = MyRng.Offset(1).Resize(MyRng.Rows.Count - 1, 2)
  Set Dic = CreateObject("Scripting.dictionary")
  Arr = MyRng
  For i = 1 To UBound(Arr)
      If Not Dic.exists(Arr(i, 1)) Then
      Dic.Add Arr(i, 1), Arr(i, 2)
      Else
      Dic.Item(Arr(i, 1)) = Dic.Item(Arr(i, 1)) + Arr(i, 2)
      End If
  Next i
  Sheet2.Range("A1") = "subject"
  Sheet2.Range("A2").Resize(Dic.Count) =   Application.WorksheetFunction.Transpose(Dic.keys)
  Sheet2.Range("B1") = "subtotal"
  Sheet2.Range("B2").Resize(Dic.Count) =   Application.WorksheetFunction.Transpose(Dic.items)
  Set Dic = Nothing
  End Sub

df.query('salary>8000   and salary<10000')                                    #过滤，df是DataFrame类型

df.sort_values(by="salary",ascending   = True)                              #排序

df.groupby("deptid")['salary'].agg([len,   np.sum, np.mean])      #分组汇总

import pandas as pd

df=pd.read_csv('D:/emp.csv')     #return    as a DataFrame

ID

ANOMALIES

ID

ANOMOLIES

import pandas as pd

import numpy as np

split_field = pd.read_csv('C:\\split_field.csv',sep='\t')

split_dict = split_field.set_index('ID').T.to_dict('list')

split_list = []

for key,value in split_dict.items():

    anomalies = value[0].split(' ')

    key_array = np.tile(key,len(anomalies))

    split_df =   pd.DataFrame(np.array([key_array,anomalies]).T,columns=['ID','ANOMALIES'])

    split_list.append(split_df)

split_field = pd.concat(split_list,ignore_index=True)

print(split_field)

Date

name

name

begin

end

import pandas as pd

import numpy as np

duty = pd.read_csv('C:\\duty.csv',sep='\t')

name_rec = ''

start = 0

duty_list = []

for i in range(len(duty)):

    if name_rec == '':

        name_rec = duty['name'][i]

    if name_rec != duty['name'][i]:

        begin =   duty['date'].loc[start:i-1].values[0]

        end =   duty['date'].loc[start:i-1].values[-1]

          duty_list.append([name_rec,begin,end])

        start = i

        name_rec = duty['name'][i]

begin = duty['date'].loc[start:i].values[0]

end = duty['date'].loc[start:i].values[-1]

duty_list.append([name_rec,begin,end])

duty_b_e = pd.DataFrame(duty_list,columns=['name','begin','end'])

print(duty_b_e)

import pandas as pd

import numpy as np

chunksize=1000000

order_data = pd.read_csv(d:\\orders.csv',iterator=True,chunksize=chunksize)

group_list = []

for chunk in order_data:

    for_inter_list = []

    top_n =   chunk.groupby(by='sellerid',as_index=False)

    for index,group in top_n:

        group = group.sort_values(by='amount',ascending=False).iloc[:3]

          for_inter_list.append(group)

    for_inter_df =   pd.concat(for_inter_list,ignore_index=True)   

    group_list.append(for_inter_df)

top_n_gr = pd.concat(group_list,ignore_index=True).groupby(by='sellerid',as_index=False)

top_n_list=[]

for index,group in top_n_gr:

    group =   group.sort_values(by='amount',ascending=False).iloc[:3]

    top_n_list.append(group)

top_3 = pd.concat(top_n_list)

print(top_3)

A

B

A

A

A

A

B