Liczenie mediany SQL - przyspieszenie - Thread - go-pugleaf

Thread View: pl.comp.bazy-danych.msaccess

3 messages

[T] Back to Threads [V] Tree View [G] Group Articles

3 total messages Started by minfo Fri, 18 Aug 2017 07:01

Liczenie mediany SQL - przyspieszenie

#137494

Author: minfo
Date: Fri, 18 Aug 2017 07:01

107 lines
3555 bytes

Dzień dobry,
Czy można jakoś przyspieszyć poniższe funkcje liczenia mediany?


Function DMediana(sql, ParamArray args())
  Dim db As Database
  Dim qr As QueryDef
  Dim rs As Recordset
  Dim pr As Parameter


  Dim i As Long, k As Long


  Set db = DBEngine(0)(0)
  Set qr = db.CreateQueryDef("", sql)


  For i = 0 To UBound(args)
    Set pr = qr.Parameters(i)
    pr.Value = args(i)
  Next


  Set rs = qr.OpenRecordset
  With rs
    If Not (.BOF And .EOF) Then
      .MoveLast
      k = .RecordCount
      If k Mod 2 = 1 Then
        .MoveFirst
        .Move (k - 1) / 2
        DMediana = .Fields(0)
      Else
        .MoveFirst
        .Move k / 2 - 1
        DMediana = .Fields(0)
        .MoveNext
        DMediana = (DMediana + .Fields(0)) / 2
      End If


    Else
      DMediana = Null
    End If
  End With


End Function




Sub CMEDIA()
On Error GoTo ErrMsg

Dim strSQLDM As String
Dim strFile As String
Dim strFtext As String
Dim db As Database
Dim rs As DAO.Recordset
Dim fs As Object
Dim csvFMedia As Object
Dim i As Integer
Dim v As Variant

Dim sUser As String
sUser = VBGetUserName()

Set db = CurrentDb

strSQLDM = "SELECT t.PRODUKT, DMEDIANA(""SELECT MM FROM qryMEDIANA WHERE PRODUKT = [p1] ORDER BY MM"", " & _
"t.PRODUKT ) AS MEDIANA FROM qryMEDIANA AS t GROUP BY t.PRODUKT;"

'strSQLDM = "SELECT t.PRODUKT, DMEDIANA(""SELECT MM FROM PRECYZJA3 WHERE PRODUKT = [p1] ORDER BY MM"", " & _
'"t.PRODUKT ) AS MEDIANA FROM PRECYZJA3 AS t GROUP BY t.PRODUKT;"

DoCmd.Hourglass (True)

Set rs = db.OpenRecordset(strSQLDM)

If rs.BOF Or rs.EOF Then
Exit Sub
Else
    
    v = SysCmd(acSysCmdInitMeter, "Generowanie mediany:", rs.RecordCount)
    i = 1

    rs.MoveFirst
    
    Do Until rs.EOF
        v = SysCmd(acSysCmdUpdateMeter, i)
        i = i + 1
   
        If strFtext = "" Then
        strFtext = "Produkt;Mediana"
        strFtext = strFtext & vbCrLf & "'" & rs.Fields("PRODUKT") & ";" & rs.Fields("MEDIANA")
        Else
        strFtext = strFtext & vbCrLf & "'" & rs.Fields("PRODUKT") & ";" & rs.Fields("MEDIANA")
        End If
        
        rs.MoveNext
    Loop


pozdrawiam
MF

Re: Liczenie mediany SQL - przyspieszenie

#137496

Author: md
Date: Fri, 18 Aug 2017 07:57

116 lines
3591 bytes

W dniu 18-08-2017 o 09:01, minfo@onet.pl pisze:
> Dzień dobry,
> Czy można jakoś przyspieszyć poniższe funkcje liczenia mediany?
>
>
> Function DMediana(sql, ParamArray args())
>   Dim db As Database
>   Dim qr As QueryDef
>   Dim rs As Recordset
>   Dim pr As Parameter
>
>
>   Dim i As Long, k As Long
>
>
>   Set db = DBEngine(0)(0)
>   Set qr = db.CreateQueryDef("", sql)
>
>
>   For i = 0 To UBound(args)
>     Set pr = qr.Parameters(i)
>     pr.Value = args(i)
>   Next
>
>
>   Set rs = qr.OpenRecordset
>   With rs
>     If Not (.BOF And .EOF) Then
>       .MoveLast
>       k = .RecordCount
>       If k Mod 2 = 1 Then
>         .MoveFirst
>         .Move (k - 1) / 2
>         DMediana = .Fields(0)
>       Else
>         .MoveFirst
>         .Move k / 2 - 1
>         DMediana = .Fields(0)
>         .MoveNext
>         DMediana = (DMediana + .Fields(0)) / 2
>       End If
>
>
>     Else
>       DMediana = Null
>     End If
>   End With
>
>
> End Function
>
>
>
>
> Sub CMEDIA()
> On Error GoTo ErrMsg
>
> Dim strSQLDM As String
> Dim strFile As String
> Dim strFtext As String
> Dim db As Database
> Dim rs As DAO.Recordset
> Dim fs As Object
> Dim csvFMedia As Object
> Dim i As Integer
> Dim v As Variant
>
> Dim sUser As String
> sUser = VBGetUserName()
>
> Set db = CurrentDb
>
> strSQLDM = "SELECT t.PRODUKT, DMEDIANA(""SELECT MM FROM qryMEDIANA WHERE PRODUKT = [p1] ORDER BY MM"", " & _
> "t.PRODUKT ) AS MEDIANA FROM qryMEDIANA AS t GROUP BY t.PRODUKT;"
>
> 'strSQLDM = "SELECT t.PRODUKT, DMEDIANA(""SELECT MM FROM PRECYZJA3 WHERE PRODUKT = [p1] ORDER BY MM"", " & _
> '"t.PRODUKT ) AS MEDIANA FROM PRECYZJA3 AS t GROUP BY t.PRODUKT;"
>
> DoCmd.Hourglass (True)
>
> Set rs = db.OpenRecordset(strSQLDM)
>
> If rs.BOF Or rs.EOF Then
> Exit Sub
> Else
>
>     v = SysCmd(acSysCmdInitMeter, "Generowanie mediany:", rs.RecordCount)
>     i = 1
>
>     rs.MoveFirst
>
>     Do Until rs.EOF
>         v = SysCmd(acSysCmdUpdateMeter, i)
>         i = i + 1
>
>         If strFtext = "" Then
>         strFtext = "Produkt;Mediana"
>         strFtext = strFtext & vbCrLf & "'" & rs.Fields("PRODUKT") & ";" & rs.Fields("MEDIANA")
>         Else
>         strFtext = strFtext & vbCrLf & "'" & rs.Fields("PRODUKT") & ";" & rs.Fields("MEDIANA")
>         End If
>
>         rs.MoveNext
>     Loop
>
>
> pozdrawiam
> MF
>
taka sugestia :
czy nie lepiej napisać kwerendę uprządkowaną rosnąco lub malejąco
policzyć rekordy
wyznaczyć środkowy
pobrać jego wartość

md

Re: Liczenie mediany SQL - przyspieszenie

#137515

Author: minfo
Date: Fri, 18 Aug 2017 08:02

14 lines
1775 bytes

> >
> taka sugestia :
> czy nie lepiej napisać kwerendę uprządkowaną rosnąco lub malejąco
> policzyć rekordy
> wyznaczyć środkowy
> pobrać jego wartość
> 
> md

Niestety jest wiele produktów 1 do kilku tys i
każdy ma po kilka kilkadziesiąt partii.
W Excelu jest to trochę żmudne ...

MF

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads