Kalendarz w MySql
Thursday, March 4th, 2010 | Uncategorized | No Comments
create table firstday (data date);
insert into firstday values (’2010-02-20′);
DROP TABLE jednosci;
CREATE TABLE jednosci (jednosci int);
INSERT INTO jednosci VALUES (1);
INSERT INTO jednosci VALUES (2);
INSERT INTO jednosci VALUES (3);
INSERT INTO jednosci VALUES (4);
INSERT INTO jednosci VALUES (5);
INSERT INTO jednosci VALUES (6);
INSERT INTO jednosci VALUES (7);
INSERT INTO jednosci VALUES (8);
INSERT INTO jednosci VALUES (9);
INSERT INTO jednosci VALUES (10);
drop table dziesiatki;
CREATE TABLE dziesiatki (dziesiatki int);
INSERT INTO dziesiatki VALUES (0);
INSERT INTO dziesiatki VALUES (10);
INSERT INTO dziesiatki VALUES (20);
INSERT INTO dziesiatki VALUES (30);
INSERT INTO dziesiatki VALUES (40);
INSERT INTO dziesiatki VALUES (50);
INSERT INTO dziesiatki VALUES (60);
INSERT INTO dziesiatki VALUES (70);
INSERT INTO dziesiatki VALUES (80);
INSERT INTO dziesiatki VALUES (90);
drop table setki;
CREATE TABLE setki (setki int);
INSERT INTO setki VALUES (00);
INSERT INTO setki VALUES (100);
INSERT INTO setki VALUES (200);
INSERT INTO setki VALUES (300);
INSERT INTO setki VALUES (400);
INSERT INTO setki VALUES (500);
INSERT INTO setki VALUES (600);
INSERT INTO setki VALUES (700);
INSERT INTO setki VALUES (800);
INSERT INTO setki VALUES (900);
DROP TABLE tysiac;
CREATE TABLE tysiac AS ( SELECT jednosci+dziesiatki+setki AS x FROM jednosci CROSS JOIN dziesiatki cross join setki ORDER BY x);
select * from tysiac
create table calendar as( select
ADDDATE(data, x) as data from
firstday a cross join
tysiac b);
Miary pozycyjne, sas, teradata
Saturday, November 21st, 2009 | SAS | No Comments
Dla sasa gotowy do odpalenia kod:
* SAS
data baza;
input x best12.;
cards;
2
3
1
4
5
6
7
8
9
10
;run;
proc sort data = baza; by x; run;
data baza_pozycja;
set baza NOBS=count;
kwantyl = round(_n_/(count/4) + 0.4999, 1);
decyl = round(_n_/(count/10)+ 0.4999,1);
run;
Dla teradaty, wymagana teabelka o nazwie baza, zaierająca kolumny id int, x int.
* TERADATA
alter table baza add kwantyl_ncr byteint;
alter table baza add decyl_ncr byteint;update baza from
(sel
a.id
quantile(4,x) as kwantyl,
quantile(10, x) as decyl
from baza a)u
set kwantyl_ncr = u.kwantyl, decyl_ncr = u.decyl
where baza .id= u.id
Make wykres transparent ;>
Friday, July 10th, 2009 | VBA | No Comments
Sub make_chart_transparent()
With ActiveChart
.ChartArea.Border.LineStyle = xlNone
.ChartArea.Interior.ColorIndex = xlNone
.PlotArea.Border.LineStyle = xlNone
.PlotArea.Interior.ColorIndex = xlNone
.Legend.Delete
End With
With ActiveChart.Axes(xlCategory)
.MajorTickMark = xlNone
.MajorTickMark = xlNone
.TickLabelPosition = xlNone
End With
With ActiveChart.Axes(xlValue)
.MajorTickMark = xlNone
.MajorTickMark = xlNone
.TickLabelPosition = xlNone
End With
For Each ax In ActiveChart.Axes
ax.HasMajorGridlines = False
ax.Border.ColorIndex = xlNone
Next
For Each sr In ActiveChart.SeriesCollection
sr.Border.LineStyle = xlNone
sr.HasDataLabels = False
Next
End Sub
Kolorowanie punktów danych w serii, wykres xls
Sub kolorowanie_serii()
Dim sr As Seriesodliczaj = val(InputBox(”Odliczaj co -n- kolumn”))
With ActiveChart
For Each sr In .SeriesCollection
For i = LBound(sr.Values) To UBound(sr.Values)
If i Mod odliczaj = 0 Then
sr.Points(i).Interior.ColorIndex = 35
End If
Next i
Next
End With
End Sub
VBA - Grupowanie kolorem
Wednesday, July 8th, 2009 | VBA | 1 Comment
Sub grupuj_kolorem()
Dim row As Range
Dim target As Range
Dim x As Integer
Dim licznik As Integercol_number = Val(InputBox(”Podaj numer kolumny do grupowania”))
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).SelectFor Each row In Selection.Rows
Set target = row.Range(Cells(1, col_number), Cells(1, col_number))
If target.Value <> target.Offset(-1, 0).Value Then
x = Abs(x - 1)
licznik = licznik + 1
End If
If x = 0 Then
row.Interior.ColorIndex = 35
Else
row.Interior.ColorIndex = 37
End If
Next
MsgBox (”Liczba Grup: ” & licznik)
End Sub
Excel - oznacz myszką wybrany wiersz
Thursday, June 4th, 2009 | VBA | No Comments
Private Declare Function GetKeyState Lib “user32″ (ByVal nVirtKey As Long) As Integer
Private Const kCapital = 20
Private Const kNumlock = 144
Private Const TargetCol = 3
Public Function CapsLock() As Boolean
CapsLock = KeyState(kCapital)
End Function
Private Function KeyState(lKey As Long) As Boolean
KeyState = CBool(GetKeyState(lKey))
End Function
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If CapsLock Then
For Each cell In Target.Cells
x = ActiveSheet.Cells(cell.Row, TargetCol)
x = Abs(x - 1)
ActiveSheet.Cells(cell.Row, TargetCol).Value = x
Next
End If
End Sub
Sub dodaj_formatowanie()
Range(”A1:D31″).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=”=$D1=1″
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub
Uzupełnienie braków w ciągłości, kumulacja
Thursday, May 28th, 2009 | Uncategorized | 1 Comment
data baza;
input dzien kumulacja;
cards;
1 1
2 3
8 4
10 8
15 9
22 15
27 18
30 20
;run;
data temp;
set baza;
output;
ile_brakuje = max(((lag(dzien)-dzien)+1)*-1,0);
do x = 1 to ile_brakuje;
nowy_dzien = dzien - x;
drop ile_brakuje x;
output;
end;
run;
data nowa_baza;
set temp;
dzien = coalesce(nowy_dzien,dzien);
keep kumulacja dzien;
run;
proc sort data= nowa_baza;
by dzien;
run;
Top N wartości dla każdej grupy w pojedyńczym zapytaniu
Thursday, May 21st, 2009 | Uncategorized | No Comments
w pojedyńczym zapytaniu, wow
http://rickosborne.org/blog/index.php/2008/01/07/sql-getting-top-n-rows-for-a-grouped-query/
SELECT c.*, d.ranknum
FROM girl AS c
INNER JOIN (
SELECT a.id, COUNT(*) AS ranknum
FROM girl AS a
INNER JOIN girl AS b ON (a.hair = b.hair) AND (a.score <= b.score)
GROUP BY a.id
HAVING COUNT(*) <= 3
) AS d ON (c.id = d.id)
ORDER BY c.hair, d.ranknum
Nowości w Datamining
Monday, March 9th, 2009 | Data Mining | No Comments
Nowe oprogramowanie Wolframa wyławia nieustrukturyzowane dane z Sieci, strukturyzuje je, uruchamia na nich swoje algorytmy i w efekcie generuje „fakty” i „odpowiedzi” w odpowiedzi na zapytania zadawane poprzez przypominający Google formularz wyszukiwania.