SQL da GROUP BY dan to`g`ri foydalanish

Assalomu alaykum. Quyida 2 usul keltirilgan, Odatdada ancha kuchli programmistlar ham 1-usulni bilishadi va shundan foydalanishadi. Ammo bundan yaxshirog`i ham bor, 2- usul. hozir solishtiramiz.

/*create table computers

as 

select 1 id, 'Bekorchi' "name"

union all

select 2, 'Mikrob'

union all

select 3, 'Virus';

create table userNames

as

select 1 id, 1 computersid,'Unknown' userName

union all

select 2, 1,'Noname'

union all

select 3, 1,'Benom'

union all

select 4, 2,'Bebaho'

union all

select 5, 2,'Arzon'

union all

select 6, 3,'Ismi yo`q';

*/

-- 1-usul

select id, name, (select count(id) from usernames where computersid=c.id) usersCount 

from computers c

order by name;

-- 2-usul

select c.id, max(c.name) "name", count(u.id) usersCount 

from computers c 

left join userNames u on c.id=u.computersid

group by c.id

order by max(c.name);

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

Demak, 1- usulni tahlil qilsak:

computers tablitsasidan nechta qator chiqarish kerak bo`lsa SELECT shuncha marta takrorlanadi. Masalan: 1000 ta qator chiqarilishi kerak bo`lsa userNames tablitsasi 1000 marta SELECT  qilinadi.

2- usulda esa. JOIN dan foydalandim. GROUP BY qo`rqinchli narsa emas - agar undan to`g`ri foydalanilsa -ya`ni bu narsa queeryni sekinlalshtirish uchun chiqarilmagan :-). 

JOIN dan foydalanganimizni asosiy sababi - 2 ta tablitsa bir biriga indexlanadi va join bo`ladi. bu nihoyatda tez bo`ladi. agarda row lar soni ko`p bo`lsa yaqqol bilinadi. Gruppirovkani Integer polya bo`yicha qilganimiz uchun juda kam vaqt sarflanadi. bu yerda max lar uchun deyarli vaqt ketmaydi(pastda tushintirilgan).

Shu o`rinda GROUP BY haqida ma`lumot berib o`tmoqchimiz. Agarda c.id bo`yicha gruppirovka qilinsa, demak bizga c.name bo`yicha yoki computers tablitsasining biror boshqa columni  bo`yicha gruppirovka qilishimizni keragi yo`q. Chunki 1 ta c.id gruppirovka qilinsa yetarli- ya`ni 1 ta c.id ga bitta qator ya`ni bitta row(zapis) to`g`ri keladi. 

Yanada soddaroq tushuntiramiz-  nima uchun max degan aggregate funksiyadan foydalandik, o`rniga min qo`ysak bo`lmasmikan?  Bo`ladi. Nega? Asosiy narsa ham shunda! Chunki c.id bilan gruppirovka qilinganda o`zi har bir c.id ga bittadan c.name to`g`ri kelib qolgandiku, demak max(c.name)=min(c.name), agar bu qator varchar emas integer bo`lsa sum ni ham foydalansa xato bo`lmaydi. Ya`ni 1 ta sonni maximum, minimum, sum lari bir xil!(bu yerda max lar uchun deyarli vaqt ketmaydi)

Agar max, min qo`ymasak bo`lmaydi. quyidagi xatolik chiqadi:

ERROR:  column "c.name" must appear in the GROUP BY clause or be used in an aggregate function

LINE 28: select c.id, c.name "name", count(u.id) usersCount 

Odatda bu xatolikni ko`rgan dasturchi darhol c.name ni GROUP BY ga qo`shib qo`yar ekan. Bu katta logic xato!

ClientHttpRequest

Googlega biror ma`lumot qidiruvi bo`yicha zapros berib birinchi pageda chiqqan natijalarning SAYTini olish


    private String getGoogleSearchingResults(String keyword) {
        String googleContent = ClientHttpRequest2.getWebContentByUrl("http://www.google.co.uk/search?q=" + keyword);
        return findAll(googleContent, "", "");
    }


    public String findAll(String text, String begin, String end) {
        StringBuffer collection = new StringBuffer();
        for (String temp : text.split(begin)) {
            int finish = temp.toLowerCase().indexOf(end.toLowerCase());
            if (finish > 0) {
                String tempcha = temp.substring(0, finish);
                if (!(tempcha == null || "".equals(tempcha))) {
                    tempcha = tempcha.split("/")[0];
                    collection.append(tempcha + " &nbsp ");
                }
            }
        }
        return collection.toString();
    }

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


import org.apache.commons.io.IOUtils;

import java.io.*;
import java.net.URL;
import java.net.URLConnection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Random;

/**
 *
Title: Client HTTP Request class
*
Description: this class helps to send POST HTTP requests with various form data,
 * including files. Cookies can be added to be included in the request.
*
 * @author Vlad Patryshev
 * @version 1.0
 */
public class ClientHttpRequest2 {
    URLConnection connection;
    OutputStream os = null;
    Map cookies = new HashMap();

    protected void connect() throws IOException {
        if (os == null) os = connection.getOutputStream();
    }

    protected void write(char c) throws IOException {
        connect();
        os.write(c);
    }

    protected void write(String s) throws IOException {
        connect();
        os.write(s.getBytes());
    }

    protected void newline() throws IOException {
        connect();
        write("\r\n");
    }

    protected void writeln(String s) throws IOException {
        connect();
        write(s);
        newline();
    }

    private static Random random = new Random();

    protected static String randomString() {
        return Long.toString(random.nextLong(), 36);
    }

    String boundary = "---------------------------" + randomString() + randomString() + randomString();

    private void boundary() throws IOException {
        write("--");
        write(boundary);
    }

    /**
     * Creates a new multipart POST HTTP request on a freshly opened URLConnection
     *
     * @param connection an already open URL connection
     * @throws IOException
     */
    public ClientHttpRequest2(URLConnection connection) throws IOException {
        this.connection = connection;
        connection.setDoOutput(true);
        connection.setRequestProperty("Content-Type",
                "multipart/form-data; boundary=" + boundary);
    }

    /**
     * Creates a new multipart POST HTTP request for a specified URL
     *
     * @param url the URL to send request to
     * @throws IOException
     */
    public ClientHttpRequest2(URL url) throws IOException {
        this(url.openConnection());
    }

    /**
     * Creates a new multipart POST HTTP request for a specified URL string
     *
     * @param urlString the string representation of the URL to send request to
     * @throws IOException
     */
    public ClientHttpRequest2(String urlString) throws IOException {
        this(new URL(urlString));
    }


    private void postCookies() {
        StringBuffer cookieList = new StringBuffer();

        for (Iterator i = cookies.entrySet().iterator(); i.hasNext(); ) {
            Map.Entry entry = (Map.Entry) (i.next());
            cookieList.append(entry.getKey().toString() + "=" + entry.getValue());

            if (i.hasNext()) {
                cookieList.append("; ");
            }
        }
        if (cookieList.length() > 0) {
            connection.setRequestProperty("Cookie", cookieList.toString());
        }
    }

    /**
     * adds a cookie to the requst
     *
     * @param name  cookie name
     * @param value cookie value
     * @throws IOException
     */
    public void setCookie(String name, String value) throws IOException {
        cookies.put(name, value);
    }

    /**
     * adds cookies to the request
     *
     * @param cookies the cookie "name-to-value" map
     * @throws IOException
     */
    public void setCookies(Map cookies) throws IOException {
        if (cookies == null) return;
        this.cookies.putAll(cookies);
    }

    /**
     * adds cookies to the request
     *
     * @param cookies array of cookie names and values (cookies[2*i] is a name, cookies[2*i + 1] is a value)
     * @throws IOException
     */
    public void setCookies(String[] cookies) throws IOException {
        if (cookies == null) return;
        for (int i = 0; i < cookies.length - 1; i += 2) {
            setCookie(cookies[i], cookies[i + 1]);
        }
    }

    private void writeName(String name) throws IOException {
        newline();
        write("Content-Disposition: form-data; name=\"");
        write(name);
        write('"');
    }

    /**
     * adds a string parameter to the request
     *
     * @param name  parameter name
     * @param value parameter value
     * @throws IOException
     */
    public void setParameter(String name, String value) throws IOException {
        boundary();
        writeName(name);
        newline();
        newline();
        writeln(value);
    }

    private static void pipe(InputStream in, OutputStream out) throws IOException {
        byte[] buf = new byte[500000];
        int nread;
        int navailable;
        int total = 0;
        synchronized (in) {
            while ((nread = in.read(buf, 0, buf.length)) >= 0) {
                out.write(buf, 0, nread);
                total += nread;
            }
        }
        out.flush();
        buf = null;
    }

    /**
     * adds a file parameter to the request
     *
     * @param name     parameter name
     * @param filename the name of the file
     * @param is       input stream to read the contents of the file from
     * @throws IOException
     */
    public void setParameter(String name, String filename, InputStream is) throws IOException {
        boundary();
        writeName(name);
        write("; filename=\"");
        write(filename);
        write('"');
        newline();
        write("Content-Type: ");
        String type = connection.guessContentTypeFromName(filename);
        if (type == null) type = "application/octet-stream";
        writeln(type);
        newline();
        pipe(is, os);
        newline();
    }

    /**
     * adds a file parameter to the request
     *
     * @param name parameter name
     * @param file the file to upload
     * @throws IOException
     */
    public void setParameter(String name, File file) throws IOException {
        setParameter(name, file.getPath(), new FileInputStream(file));
    }

    public void setParametrsURL(String name, String fileName, String link) throws IOException {
        InputStream is = new URL(link).openStream();
        setParameter(name, fileName, is);
    }

    /**
     * adds a parameter to the request; if the parameter is a File, the file is uploaded, otherwise the string value of the parameter is passed in the request
     *
     * @param name   parameter name
     * @param object parameter value, a File or anything else that can be stringified
     * @throws IOException
     */
    public void setParameter(String name, Object object) throws IOException {
        if (object instanceof File) {
            setParameter(name, (File) object);
        } else {
            setParameter(name, object.toString());
        }
    }

    /**
     * adds parameters to the request
     *
     * @param parameters "name-to-value" map of parameters; if a value is a file, the file is uploaded, otherwise it is stringified and sent in the request
     * @throws IOException
     */
    public void setParameters(Map parameters) throws IOException {
        if (parameters == null) return;
        for (Iterator i = parameters.entrySet().iterator(); i.hasNext(); ) {
            Map.Entry entry = (Map.Entry) i.next();
            setParameter(entry.getKey().toString(), entry.getValue());
        }
    }

    /**
     * adds parameters to the request
     *
     * @param parameters array of parameter names and values (parameters[2*i] is a name, parameters[2*i + 1] is a value); if a value is a file, the file is uploaded, otherwise it is stringified and sent in the request
     * @throws IOException
     */
    public void setParameters(Object[] parameters) throws IOException {
        if (parameters == null) return;
        for (int i = 0; i < parameters.length - 1; i += 2) {
            setParameter(parameters[i].toString(), parameters[i + 1]);
        }
    }

    /**
     * posts the requests to the server, with all the cookies and parameters that were added
     *
     * @return input stream with the server response
     * @throws IOException
     */
    public InputStream post() throws IOException {
        boundary();
        writeln("--");
        os.close();
        return connection.getInputStream();
    }

    /**
     * posts the requests to the server, with all the cookies and parameters that were added before (if any), and with parameters that are passed in the argument
     *
     * @param parameters request parameters
     * @return input stream with the server response
     * @throws IOException
     * @see setParameters
     */
    public InputStream post(Map parameters) throws IOException {
        setParameters(parameters);
        return post();
    }

    /**
     * posts the requests to the server, with all the cookies and parameters that were added before (if any), and with parameters that are passed in the argument
     *
     * @param parameters request parameters
     * @return input stream with the server response
     * @throws IOException
     * @see setParameters
     */
    public InputStream post(Object[] parameters) throws IOException {
        setParameters(parameters);
        return post();
    }

    /**
     * posts the requests to the server, with all the cookies and parameters that were added before (if any), and with cookies and parameters that are passed in the arguments
     *
     * @param cookies    request cookies
     * @param parameters request parameters
     * @return input stream with the server response
     * @throws IOException
     * @see setParameters
     * @see setCookies
     */
    public InputStream post(Map cookies, Map parameters) throws IOException {
        setCookies(cookies);
        setParameters(parameters);
        return post();
    }

    /**
     * posts the requests to the server, with all the cookies and parameters that were added before (if any), and with cookies and parameters that are passed in the arguments
     *
     * @param cookies    request cookies
     * @param parameters request parameters
     * @return input stream with the server response
     * @throws IOException
     * @see setParameters
     * @see setCookies
     */
    public InputStream post(String[] cookies, Object[] parameters) throws IOException {
        setCookies(cookies);
        setParameters(parameters);
        return post();
    }

    /**
     * post the POST request to the server, with the specified parameter
     *
     * @param name  parameter name
     * @param value parameter value
     * @return input stream with the server response
     * @throws IOException
     * @see setParameter
     */
    public InputStream post(String name, Object value) throws IOException {
        setParameter(name, value);
        return post();
    }

    /**
     * post the POST request to the server, with the specified parameters
     *
     * @param name1  first parameter name
     * @param value1 first parameter value
     * @param name2  second parameter name
     * @param value2 second parameter value
     * @return input stream with the server response
     * @throws IOException
     * @see setParameter
     */
    public InputStream post(String name1, Object value1, String name2, Object value2) throws IOException {
        setParameter(name1, value1);
        return post(name2, value2);
    }

    /**
     * post the POST request to the server, with the specified parameters
     *
     * @param name1  first parameter name
     * @param value1 first parameter value
     * @param name2  second parameter name
     * @param value2 second parameter value
     * @param name3  third parameter name
     * @param value3 third parameter value
     * @return input stream with the server response
     * @throws IOException
     * @see setParameter
     */
    public InputStream post(String name1, Object value1, String name2, Object value2, String name3, Object value3) throws IOException {
        setParameter(name1, value1);
        return post(name2, value2, name3, value3);
    }

    /**
     * post the POST request to the server, with the specified parameters
     *
     * @param name1  first parameter name
     * @param value1 first parameter value
     * @param name2  second parameter name
     * @param value2 second parameter value
     * @param name3  third parameter name
     * @param value3 third parameter value
     * @param name4  fourth parameter name
     * @param value4 fourth parameter value
     * @return input stream with the server response
     * @throws IOException
     * @see setParameter
     */
    public InputStream post(String name1, Object value1, String name2, Object value2, String name3, Object value3, String name4, Object value4) throws IOException {
        setParameter(name1, value1);
        return post(name2, value2, name3, value3, name4, value4);
    }

    /**
     * posts a new request to specified URL, with parameters that are passed in the argument
     *
     * @param parameters request parameters
     * @return input stream with the server response
     * @throws IOException
     * @see setParameters
     */
    public static InputStream post(URL url, Map parameters) throws IOException {
        return new ClientHttpRequest2(url).post(parameters);
    }

    /**
     * posts a new request to specified URL, with parameters that are passed in the argument
     *
     * @param parameters request parameters
     * @return input stream with the server response
     * @throws IOException
     * @see setParameters
     */
    public static InputStream post(URL url, Object[] parameters) throws IOException {
        return new ClientHttpRequest2(url).post(parameters);
    }

    /**
     * posts a new request to specified URL, with cookies and parameters that are passed in the argument
     *
     * @param cookies    request cookies
     * @param parameters request parameters
     * @return input stream with the server response
     * @throws IOException
     * @see setCookies
     * @see setParameters
     */
    public static InputStream post(URL url, Map cookies, Map parameters) throws IOException {
        return new ClientHttpRequest2(url).post(cookies, parameters);
    }

    /**
     * posts a new request to specified URL, with cookies and parameters that are passed in the argument
     *
     * @param cookies    request cookies
     * @param parameters request parameters
     * @return input stream with the server response
     * @throws IOException
     * @see setCookies
     * @see setParameters
     */
    public static InputStream post(URL url, String[] cookies, Object[] parameters) throws IOException {
        return new ClientHttpRequest2(url).post(cookies, parameters);
    }

    /**
     * post the POST request specified URL, with the specified parameter
     *
     * @param name  parameter name
     * @param value parameter value
     * @return input stream with the server response
     * @throws IOException
     * @see setParameter
     */
    public static InputStream post(URL url, String name1, Object value1) throws IOException {
        return new ClientHttpRequest2(url).post(name1, value1);
    }

    /**
     * post the POST request to specified URL, with the specified parameters
     *
     * @param name1  first parameter name
     * @param value1 first parameter value
     * @param name2  second parameter name
     * @param value2 second parameter value
     * @return input stream with the server response
     * @throws IOException
     * @see setParameter
     */
    public static InputStream post(URL url, String name1, Object value1, String name2, Object value2) throws IOException {
        return new ClientHttpRequest2(url).post(name1, value1, name2, value2);
    }

    /**
     * post the POST request to specified URL, with the specified parameters
     *
     * @param name1  first parameter name
     * @param value1 first parameter value
     * @param name2  second parameter name
     * @param value2 second parameter value
     * @param name3  third parameter name
     * @param value3 third parameter value
     * @return input stream with the server response
     * @throws IOException
     * @see setParameter
     */
    public static InputStream post(URL url, String name1, Object value1, String name2, Object value2, String name3, Object value3) throws IOException {
        return new ClientHttpRequest2(url).post(name1, value1, name2, value2, name3, value3);
    }

    /**
     * post the POST request to specified URL, with the specified parameters
     *
     * @param name1  first parameter name
     * @param value1 first parameter value
     * @param name2  second parameter name
     * @param value2 second parameter value
     * @param name3  third parameter name
     * @param value3 third parameter value
     * @param name4  fourth parameter name
     * @param value4 fourth parameter value
     * @return input stream with the server response
     * @throws IOException
     * @see setParameter
     */
    public static InputStream post(URL url, String name1, Object value1, String name2, Object value2, String name3, Object value3, String name4, Object value4) throws IOException {
        return new ClientHttpRequest2(url).post(name1, value1, name2, value2, name3, value3, name4, value4);
    }

    public StringBuffer getResult() throws IOException {
        BufferedReader rd = new BufferedReader(new InputStreamReader(post()));
        String line = "";
        StringBuffer response = new StringBuffer();
        while ((line = rd.readLine()) != null) {
            response.append(line + '\r');
        }
        rd.close();
        connection.getInputStream().close();
        return response;
    }

    public static String getWebContentByUrl(String link) {
        try {
            URL url = new URL(link);
            URLConnection urlConnection = url.openConnection();
            urlConnection.setUseCaches(false);
            urlConnection.setRequestProperty(
                    "User-Agent",
                    "Mozilla/5.0 (X11; U; Linux x86_64; en-GB; rv:1.8.1.6) Gecko/20070723 Iceweasel/2.0.0.6 (Debian-2.0.0.6-0etch1)");
//            StringBuffer buffer = new StringBuffer();
//            BufferedReader in = new BufferedReader(new InputStreamReader(urlConnection.getInputStream()));
//            String inputLine;
            InputStream inputStream = urlConnection.getInputStream();
            String temp = IOUtils.toString(inputStream);
            inputStream.close();
            return temp;
//            while ((inputLine = in.readLine()) != null) {
//                buffer.append(inputLine);
//            }
//            in.close();
//            return buffer.toString();
        } catch (Exception exp) {
            return exp.getMessage();
        }
    }
}